Group by with subdivisions

Question

I have an SQL that brings the number of people who graduated on a given date. Let’s say that of 300 people, 25 graduated on '2010-06-27'.

I would like to include two more columns that return the number of men and women of these 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 1

To get the total amount of men and women, make 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