I have 3 columns and i want to count how many time col2 = 0 AND col3 = 0 for all disctinct element in col1.
Ex:
| COL1 | COL2 | COL3 |
|---|---|---|
| alpha | 1 | 1 |
| alpha | 0 | 0 |
| beta | 0 | 0 |
| gamma | 3 | 2 |
| alpha | 3 | 4 |
| gamma | 0 | 0 |
| delta | 0 | 0 |
| omega | 4 | 4 |
| omega | 1 | 0 |
| alpha | 0 | 0 |
| delta | 0 | 0 |
Result expected :
| COL1 | COL2 | COL3 | OCCURENCE |
|---|---|---|---|
| alpha | 0 | 0 | 2 |
| beta | 0 | 0 | 1 |
| delta | 0 | 0 | 2 |
| gamma | 0 | 0 | 1 |
| omega | 0 | 0 | 0 |
I tried that but it's not working:
SELECT DISTINCT col1, col2, col3, COUNT(*) FROM table1 WHERE col2 = 0 AND col3 = 0
CodePudding user response：
Do a GROUP BY. Use a case expression to do conditional aggregation.
SELECT col1, 0 as col2, 0 as col3,
sum(case when col2 = 0 AND col3 = 0 then 1 else 0 end) as OCCURENCE
FROM table
GROUP BY col1
CodePudding user response：
You need to get rid of DISTINCT and add GROUP BY. Then to add occurence of col1 that doesn't match your criteria you need to add it based on results you got using UNION. Your first query can also be put in CTE for easier use
with cte as (
SELECT col1, col2, col3, COUNT(*) as OCCURENCE
FROM #table
WHERE col2 = 0 AND col3 = 0
GROUP BY col1, col2, col3
)
SELECT * from cte
UNION all
SELECT DISTINCT col1, 0, 0, 0 FROM table1 where col1 NOT IN (select col1 from cte)