I have a set of data that I want to count by multiple characteristics.
I am starting with something likes this (True/False for catagories)
| week | PP | SEN |
|---|---|---|
| 1 | T | F |
| 1 | T | T |
| 1 | F | F |
| 2 | T | F |
| 2 | T | T |
| 2 | F | F |
| 2 | F | F |
| 3 | T | F |
| 3 | F | F |
I want to end up with something along the lines of
| week | PP | SEN | Total |
|---|---|---|---|
| 1 | 2 | 1 | 3 |
| 2 | 2 | 1 | 4 |
| 3 | 1 | 0 | 2 |
As far as I can see I can only get something that would give me nested results
eg
| week | PP | SEN | Total |
|---|---|---|---|
| 1 | T | T | 1 |
| 1 | T | F | 1 |
| 1 | F | T | 0 |
| 1 | F | F | 1 |
It may be that this is really straight forward and I just have not found the correct search term, or it’s just impossible via a query…but all suggestions welcome.
Advertisement
Answer
I think you just want conditional aggregation:
select week, count(*) as total,
sum(case when pp = 'T' then 1 else 0 end) as num_pp,
sum(case when sen = 'T' then 1 else 0 end) as num_sen
from t
group by week;