how to add values from select statement as a new column

user_11296746 0
Viewed 7080

i have my table as

ID  FirstName   LastName    ReportsTo       Position    Age
8   Ashley      Johnson     null            Intern      25  
5   Noah        Fritz       Jenny Richards  Assistant   30  
6   David       S           Jenny Richards  Director    32  
3   Jenny       Richards    null            CEO         45

i want to add a column as "Boss title" to above table with values from below select statement. but it is not working.

SELECT *,
    case when ReportsTo='Jenny Richards' then 'CEO'
    else 'None' end 
    as 'Boss Title'
    FROM maintable_L8MOQ where ReportsTo='Jenny Richards' or ReportsTo is null order by age;
case select join sql
4 Answers

Use double quotes " around new column name. Here is the working demo.

SELECT *,
    case when ReportsTo='Jenny Richards' then 'CEO'
    else 'None' end 
    as "Boss Title"
FROM maintable_L8MOQ 
where ReportsTo='Jenny Richards' or ReportsTo is null 
order by age;
user_86913190

You can self-join the table or use a correlated subquery to bring the title of each employee's boss.

However, please note that your schema is not properly optimized for this. You should be referencing the id of the boss of each employee rather than its name: this would be more efficient (it avoids the need for string concatenation) and more accurate (there is no risk of homonyms).

For your current schema, that would be:

select l.*, coalesce(b.title, 'None') boss_title
from maintable_L8MOQ l
left join maintable_L8MOQ b 
    on concat(b.firstName, ' ', b.lastName) = l.reportsTo
order by l.age
user_106767160 
select FirstName,LastName,ReportsTO,Position,Age
      ,(case when ReportsTO='Jenny Richards' then 'CEO' 
             when ReportsTO is null then 'None' 
        else 'None' 
        end) as "Boss Title"
from maintable_H9LYD
where   ReportsTO = 'Jenny Richards' 
     or ReportsTO is null
order by age ASC
user_142097530

This is a correct answer

SELECT
  mt.ReportsTo, 
  COUNT(mt.ReportsTo) as Members, 
  ROUND(AVG(mt.AGe)) as Average_Age
FROM 
  maintable_7SKI5 mt
WHERE 
  ReportsTo is not null
GROUP BY 
  mt.ReportsTo
ORDER BY 
  mt.ReportsTo
user_86512320
Related