why do I get multiple lines for each worker_id? do I have to add 'level' column to group by? what is the correct way to write it?
here's my script:
select worker_id, AVG(CASE WHEN year(month)=2020
THEN BASIC_SALARY+bonus
ELSE NULL END) AS avg_2020,
avg(case when year(month)=2021
then BASIC_SALARY
else null end) as avg_2021,
case when t1.level='C' then 'YES'
else 'NO'
end as 'Level C',
CASE WHEN AVG(BASIC_SALARY) >6000
THEN 'yes'
ELSE 'no'
END AS 'above_6000',
count(BASIC_SALARY) 'HowManySalaries'
from [workers_table] t1
join data t2
on t1.level=t2.level
group by worker_id,t1.level