SQL - 计算一个属性，按另一个分组

Question

我有一张 table staff

staff
pt | ward
P  | 1
P  | 1
T  | 1
P  | 2
T  | 2

我想制作一个表格来计算每个病房有多少个 P 和 T，如下所示:

staff
ward | P | T
1    | 2 | 1
2    | 1 | 1

我试过了

WITH cte(ward, P, T) AS(
SELECT ward,
(SELECT COUNT(PT) FROM staff WHERE PT = 'P' ),
(SELECT COUNT(PT) FROM staff WHERE PT = 'T' ) FROM staff GROUP BY ward)
SELECT * FROM cte

然后我得到这张表

staff
ward | P | T
1    | 3 | 2
2    | 2 | 2

任何帮助将不胜感激

Answer 1

案例陈述将在这里工作:

SELECT
    ward,
    SUM(CASE WHEN pt = P THEN 1 ELSE 0 END) AS P,
    SUM(CASE WHEN pt = T THEN 1 ELSE 0 END) AS T
FROM
    table
GROUP BY
    ward