Mr. Smith Mr. Smith - 6 months ago 12
SQL Question

GROUP BY and count whether column is null

I've got a query that performs a

GROUP BY (col1, col2, col3)
and then returns the count as
groupByCount
:

+------+------+------+--------------+
| col1 | col2 | col3 | groupByCount |
+------+------+------+--------------+
| 1 | a | A | 2 |
| 2 | b | B | 4 |
| 1 | a | null | 5 |
| 2 | b | null | 3 |
+------+------+------+--------------+


That works, but it's not exactly what I'm after. I want to tally a count of where col3 was null or not null:

+------+------+------+-------------+----------+
| col1 | col2 | col3 | col3notnull | col3null |
+------+------+------+-------------+----------+
| 1 | a | A | 2 | 5 |
| 2 | b | B | 4 | 3 |
| 1 | a | null | 0 | 5 |
| 2 | b | null | 0 | 3 |
+------+------+------+-------------+----------+


Is there a way to perform this count?

Answer

I think you can do this with window functions:

select col1, col2, col3,
       sum(case when col3 is not null then count(*) end) over (partition by col1, col2) as col3notnull,
       sum(case when col3 is null then count(*) end) over (partition by col1, col2) as col3null
from t
group by col1, col2, col3;

However, I don't understand why the "not null" values are 0 but the "null" values repeat.

If the first two values in the last column should really be 0s, then:

select col1, col2, col3,
       (case when col3 is not null then count(*) else 0 end) as col3notnull,
       (case when col3 is null then count(*) else 0 end) as col3null
from t
group by col1, col2, col3;
Comments