I am working on this SQL problem for a class – "Please calculate the number of students who are male, and the number of students who are female. A student is a person with an enrollment record."
The expected result should look like this
However I have been unable to get the result to return in this format, at this point I am stuck here
Utilizing this code
SELECT
COUNT(Distinct enrollment.personID) as male
FROM dbo.enrollment
RIGHT JOIN dbo.person ON person.personID=enrollment.personID
WHERE person.gender='male'
UNION
SELECT
COUNT(Distinct enrollment.personID) as female
FROM dbo.enrollment
RIGHT JOIN dbo.person ON person.personID=enrollment.personID
WHERE person.gender='female';
Any help getting me pointed towards the correct format would be greatly appreciated.
>Solution :
Actually using a UNION running two separate queries to obtain male and female counts.
To get what you need, You have to use conditional aggregation with a CASE statement, in just one single query. Which can be more efficient and reduces the need for two separate queries:
SELECT
COUNT(DISTINCT CASE WHEN person.gender = 'male' THEN enrollment.personID END) as male,
COUNT(DISTINCT CASE WHEN person.gender = 'female' THEN enrollment.personID END) as female
FROM dbo.enrollment
RIGHT JOIN dbo.person ON person.personID = enrollment.personID;
Another more readable and easy way to solve it with subquery:
SELECT SUM(p.gender = 'male') AS male, SUM(p.gender = 'female') AS female
FROM person AS p
WHERE p.personID IN (SELECT personID FROM enrollment);