rachid el kedmiri rachid el kedmiri - 3 years ago 154
MySQL Question

NULL value count in group by

for simplification purposes, I will use simple table attribute (meaning the table is bigger) to demonstrate the issue:

I have the following table test:

id | nbr
----+-----
1 | 0
2 |
3 |
4 | 1
5 | 1
(5 rows)


id and nbr are both numeric values

The following query

select nbr, count(nbr) from test group by nbr;


outputs:

nbr | count
-----+-------
| 0
1 | 2
0 | 1
(3 rows)


whereas the query:

select nbr, count(*) from test group by nbr;


outputs:

nbr | count
-----+------
| 2
1 | 2
0 | 1
(3 rows)


I find hard to explain the difference between count(nbr) and count(*) regarding null values
can someone explain this to me like I'm five, thanks

Answer Source

It's pretty simple:

count(<expression>) counts the number of values. Like most aggregate functions, it removes null values before doing the actual aggregation.

count(*) is a special case that counts the number of rows (regardless of any null).

count (no matter if * or <expression>) never returns null (unlike most other aggregate functions). In case no rows are aggregated, the result is 0.

Now, you have done a group by on an nullable column. group by put's null values into the same group. That means, the group for nbr null has two rows. If you now apply count(nbr), the null values are removed before aggregation, giving you 0 as result.

If you would do count(id), there would be no null value to be removed, giving you 2.

This is standard SQL behavior and honored by pretty much every database.

One of the common use-cases is to emulate the filter clause in databases that don't support it natively: http://modern-sql.com/feature/filter#conforming-alternatives

The exceptions (aggregate functions that don't remove null prior to aggregation) are functions like json_arrayagg, json_objectagg, array_agg and the like.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download