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?

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;
``````
Source (Stackoverflow)