CODEWITHSUNDEEP
sqlpostgresql
tomoswizzard
tomoswizzard

Reputation: 65

SQL - Counting one attribute, grouping by another

I have a table staff

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

I want to produce a table that counts how many P's and T's there is for each ward like this:

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

I have tried this

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

but then I get this table 

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

Any help would be appreciated

Upvotes: 1

Views: 88

Answers (2)

tysonwright
tysonwright

Reputation: 1525

Case statements will work here:

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use conditional aggregation:

select ward, sum( (pt = 'P')::int ) as p, sum ( (pt = 'T')::int ) as t
from t
group by ward;

Upvotes: 0

Related Questions