sql - 如何使用 Postgres SQL 获取具有单个总计行的同一列的不同值的计数？

Question

所以我有一个 child 列表，我想创建一个列表，列出每所学校有多少男孩和女孩，以及最终的总人数。

我的查询包括逻辑

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

显然我知道这个查询不会工作，但我不知道从这里进一步做什么。我考虑过嵌套查询，但我很难让它发挥作用。

我发现了 postgres 9.4 的类似问题 Postgres nested SQL query to count field .但是我有 Postgres 9.3。

最终结果如下:

Answer 1

警告根据学校 ID 的数据类型，您可能会遇到此并集的错误。如果学校 ID 的类型为 INT，请考虑将其转换为 varchar。

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