SQLTeam.com | Weblogs | Forums

Case with froup by-multiple lines

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

Because you're also GROUP on t1.level.

How to correct it depends on what you want to show when there are different levels for the same worker_id.