Complicated Grouping - Help needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Complicated Grouping - Help needed

Author

Topic

K-SaMa
Starting Member

7 Posts

Posted - 2011-03-23 : 18:48:04

Hello guys, can anyone tell me how to write this sort of queries?

First here's how are the tables related to each other,

the query i need goes as this :

Show each JOB_ID, 
SUM of salaries of people practicing this job that are in DEPARTMENT_ID = 20,
SUM of salaries of people practicing this job that are in DEPARTMENT_ID = 50,
SUM of salaries of people practicing this job that are in DEPARTMENT_ID = 80,
SUM of salaries of people practicing this job that are in DEPARTMENT_ID = 90,
SUM of salaries of ALL people practicing this job (TOTAL)

the solution is somethign like this :

The Query I've tried goes like this :

select e.job_id "Job", sum(e20.salary)"Dept 20", sum(e50.salary)"Dept 50", sum(e80.salary)"Dept 80", sum(e90.salary)"Dept 90", sum(etotal.salary)"Total"
from employees e
left outer join employees e20 on e20.job_id=e.job_id and e20.department_id=20
left outer join employees e50 on e50.job_id=e.job_id and e50.department_id=50
left outer join employees e80 on e80.job_id=e.job_id and e80.department_id=80
left outer join employees e90 on e90.job_id=e.job_id and e90.department_id=90
left outer join employees etotal on etotal.job_id=e.job_id
group by e.job_id

but here's my result, at one line (look at highlighted values in the folowwing image), I noticed that the SUM of salaries in Dept 80 exceeds the total SUM of salaries from all departments:

what exactly am I doing wrong? thanks in advance for your help :)

K-SaMa
Starting Member

7 Posts

Posted - 2011-03-23 : 19:02:56

By The Way, I'm using oracle database hence The LEFT OUTER JOIN statement, it doesn't really matter to me which dbms you might use as long as the answer is working on it :)
Thanks in advance guys :)

singularity
Posting Yak Master

153 Posts

Posted - 2011-03-23 : 19:38:18

You don't need any joins for what you're trying to do:


select job_id as job,
       sum(case when department_id = 20 then salary else 0 end) as dept_20,
       sum(case when department_id = 50 then salary else 0 end) as dept_50,
       sum(case when department_id = 80 then salary else 0 end) as dept_80,
       sum(case when department_id = 90 then salary else 0 end) as dept_90,
       sum(salary) as total
from employees e
group by job_id

K-SaMa
Starting Member

7 Posts

Posted - 2011-03-23 : 20:12:08

I can't believe it was THAT easy damn >.>
Thanks a lot man truly apreciate it :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources