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;
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]