Search code examples
sqlpostgresqlunionpostgresql-9.3

How do I get counts for different values of the same column with a single totals row, using Postgres SQL?


So I have a list of children and I want to create a list of how many boys and girls there are in each school and a final total count of how many there are.

My query including logic

select sch.id as ID, sch.address as Address, count(p.sex for male) as boycount, count(p.sex for female) as girlcount
from student s
join school sch on sch.studentid = s.id
join person p on p.studentid = s.id

Obviously I know this query wont work but I dont know what to do further from here. I thought about nested query but im having difficulty getting it to work.

I found a similar question for postgres 9.4 Postgres nested SQL query to count field. However I have Postgres 9.3.

Final result would be like :

enter image description here


Solution

  • WARNING Depending on the data type of the school ID, you may get an error with this union. Consider casting the school ID as a varchar if it is of type INT.

    SELECT
        sch.id as ID, /*Consider casting this as a varchar if it conflicts with 
                         the 'Total' text being unioned in the next query*/
        sch.address as Address,
        SUM(CASE
                WHEN p.sex = 'male'
                THEN 1
                ELSE 0
                END) AS BoyCount,
        SUM(CASE
                WHEN p.sex = 'female'
                THEN 1
                ELSE 0
                END) AS GirlCount
    FROM
        student s
        JOIN school sch
        ON sch.studentid = s.id
        JOIN person p
        ON p.studentid = s.id
    UNION ALL
    SELECT
        'Total' as ID,
        NULL as Address,
        SUM(CASE
                WHEN p.sex = 'male'
                THEN 1
                ELSE 0
                END) AS BoyCount,
        SUM(CASE
                WHEN p.sex = 'female'
                THEN 1
                ELSE 0
                END) AS GirlCount
    FROM
        person p