SQL Server 分組統計：按區域統計總數、男數、女數

表結構

TABLE TEST
ID  NAME  SEX(BIT)  AREA
1   mr.a  1         CN
2   mr.b  1         USA
3   mr.c  1         CN
4   mr.d  0         USA
5   mr.e  0         JP
6   mr.f  1         USA

SEX=1 為男，0 為女。需求：按 AREA 分組，輸出 AREA, PEOPLE, BOY, GIRL。

標準 SQL 寫法（CASE WHEN 條件聚合）

SELECT
    AREA,
    COUNT(*) AS PEOPLE,
    SUM(CASE WHEN SEX = 1 THEN 1 ELSE 0 END) AS BOY,
    SUM(CASE WHEN SEX = 0 THEN 1 ELSE 0 END) AS GIRL
FROM TEST
GROUP BY AREA
ORDER BY AREA;

輸出結果

AREA  PEOPLE  BOY  GIRL
CN    2       2    0
USA   3       2    1
JP    1       0    1

進階：使用 FILTER 子句

-- PostgreSQL / SQLite / SQL Server 2022+
SELECT
    AREA,
    COUNT(*) AS PEOPLE,
    COUNT(*) FILTER (WHERE SEX = 1) AS BOY,
    COUNT(*) FILTER (WHERE SEX = 0) AS GIRL
FROM TEST
GROUP BY AREA;

避坑指南

• GROUP BY AREA 後 SELECT 只能出現聚合函數或分組列。
• SEX 為 BIT 類型時，CASE WHEN SEX = 1 或 CASE WHEN SEX = 0 均可；若為字符串 'M'/'F' 調整條件。
• 若需同時顯示男女比例：ROUND(100.0 * SUM(CASE WHEN SEX=1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS BOY_PCT。

擴展：多維度分組

-- 按區域+年份統計
SELECT AREA, YEAR(JOIN_DATE) AS YR,
       COUNT(*) AS PEOPLE,
       SUM(CASE WHEN SEX=1 THEN 1 ELSE 0 END) AS BOY
FROM TEST
GROUP BY AREA, YEAR(JOIN_DATE);

掌握 CASE WHEN 條件聚合模式，靈活應對各類分組統計需求。