Group by with subdivisions

Question

Group by with subdivisions

Navigation

#1 by (3 votes)

7

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

mysql sql

asked by anonymous 10.12.2015 / 14:22

1 answer

Encrypt a phrase / word by reversing and changing the vowels, by the following vowels Doubt on using LEFT JOIN in mysql

score 3 · Accepted 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