NULL value in group by

Question

to simplify, I will use a simple table attribute (which means the table is larger) to demonstrate the problem:

I have the following table test:

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

id and nbr are numeric values

Next request

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

outputs:

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

whereas the request is:

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

outputs:

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

It’s hard for me to explain the difference between count (nbr) and count (*) with respect to zero values, can someone explain this to me as if I’m five, thanks

Answer 1

It is pretty simple:

count(<expression>)counts the number of values. Like most aggregate functions, it deletes the nullvalues ​​before performing the actual aggregation.

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

count ( , * <expression>) null ( ). , 0.

, group by . group by null . , nbr null . count(nbr), , count(nbr) 0 .

count(id), null , , 2.

SQL .

filter , : http://modern-sql.com/feature/filter#conforming-alternatives

( , null ) , json_arrayagg, json_objectagg, array_agg .

Answer 2

MySQL COUNT():

COUNT(expr)

non NULL expr , SELECT.

COUNT(*) , , , NULL .

---

PostgreSQL :

NULL, , () null, . , , .

, COUNT(*) ; count(f1) , f1 , count NULL s; count(distinct f1) NULL f1.

Answer 3

count(*) , , . Inpependntly of fatc

count(nbr) , , , nbr

Answer 4

:

SELECT nbr, COUNT(*) FROM mytables WHERE nbr IS NULL GROUP BY nbr

UNION

SELECT nbr, COUNT(nbr) FROM mytables WHERE nbr IS NOT NULL GROUP BY nbr