1

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

Joshua Schlichting
  • 3,110
  • 6
  • 28
  • 54
Tito
  • 601
  • 8
  • 23

1 Answers1

1

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
Joshua Schlichting
  • 3,110
  • 6
  • 28
  • 54