Question :
I have a SQL that brings the number of people who graduated on a certain date. Let’s say that of 300 people, 25 graduated on ‘2010-06-27’.
I would like to include two more columns that will return the number of men and women of those 25 people who graduated that day.
SELECT count(*) AS 'quantidade',
data_nasc,
(SELECT count(*)
FROM formandos
WHERE genero = 'M') AS 'Quant'
FROM formandos
GROUP BY data_nasc
ORDER BY quantidade DESC
Answer :
To get the total amount of men and women, do a sub-select for each and group by date.
SELECT
COUNT(*) AS 'quantidade',
F.data_nasc,
(
SELECT
COUNT(*)
FROM
formandos AS H
WHERE
H.genero = 'M'
AND
H.data_nasc = F.data_nasc
GROUP BY F.data_nasc
) AS 'Homens',
(
SELECT
COUNT(*)
FROM
formandos AS M
WHERE
M.genero = 'F'
AND
M.data_nasc = F.data_nasc
GROUP BY M.data_nasc
) AS 'Mulheres'
FROM
formandos AS F
GROUP BY F.data_nasc
ORDER BY quantidade DESC