kakaman997 kakaman997 - 6 months ago 9
SQL Question

Oracle SQL, group by multiple group by expressions

First of all here is the data I'm working with Data.
What I want to do is for each department display the count of each relationship count:

Dnumber Sons Daughters Spouses

So far I only managed to group by relationship like that

SELECT DNUMBER DEPARTMENT_NUMBER , count(d.DNUMBER) DEPENTENT_COUNT
FROM DEPARTMENT d JOIN employee e ON d.DNUMBER = e.DNO JOIN DEPENDENT de ON de.ESSN = e.SSN
GROUP BY d.DNUMBER;


I also try creating views for each relationship type but that didn't work either.

Answer
select dnumber as department_number,
       count(case de.relationship when 'SON'      then 1 end) as sons,
       count(case de.relationship when 'DAUGHTER' then 1 end) as daughters,
       count(case de.relationship when 'SPOUSE'   then 1 end) as spouses
from   [... the rest of your query here]