Displaying null fields as 0 in mySQL select [duplicate]

Question

I have a database that collects statistics from Usenet. When I do a basic select such as:

select day, count(day) from statistics where month = 12 and year = 2020 group by day;

I get the result

+------+------------+
| day  | count(day) |
+------+------------+
|    1 |         72 |
|    2 |         82 |
|    3 |         94 |
|    4 |         73 |
|    5 |        121 |
|    6 |         42 |
|    7 |         27 |
|    8 |         26 |
|    9 |          6 |
|   14 |          6 |
|   15 |         18 |
|   16 |         23 |
|   19 |         16 |
|   18 |         90 |
|   17 |         78 |
|   20 |         42 |
|   21 |         77 |
|   22 |         57 |
|   23 |        155 |
|   24 |         85 |
|   25 |         21 |
|   26 |         40 |
|   27 |         63 |
|   28 |         90 |
|   29 |         68 |
|   30 |         49 |
|   31 |         50 |
+------+------------+

Some of the fields are where days have not been counted (10,11,12,13), so are NULL in the d'base.

How do I modify my SELECT statement so that it displays this missing info like:

|   10 |         0 |
|   11 |         0 |
|   12 |         0 |
|   13 |         0 |

to give a complete result of:

+------+------------+
| day  | count(day) |
+------+------------+
|    1 |         72 |
|    2 |         82 |
|    3 |         94 |
|    4 |         73 |
|    5 |        121 |
|    6 |         42 |
|    7 |         27 |
|    8 |         26 |
|    9 |          6 |
|   10 |          0 |
|   11 |          0 |
|   12 |          0 |
|   13 |          0 |
|   14 |          6 |
|   15 |         18 |
|   16 |         23 |
|   19 |         16 |
|   18 |         90 |
|   17 |         78 |
|   20 |         42 |
|   21 |         77 |
|   22 |         57 |
|   23 |        155 |
|   24 |         85 |
|   25 |         21 |
|   26 |         40 |
|   27 |         63 |
|   28 |         90 |
|   29 |         68 |
|   30 |         49 |
|   31 |         50 |
+------+------------+

I'm not sure if there's something to do with IFNULL or it's more complicated (I am not an expert :-)) using a JOIN.

Answer 1

It is unclear which of two problems you are asking about. I will address both.

The question of missing days requires another table:

SELECT ...
    FROM table_of_days AS d         -- pre-construct a big table of lots of days
    LEFT JOIN your_table  USING(dy)
    WHERE d.dy BETWEEN ... AND ...   -- the range of days desired for this SELECT

(This is one place where MariaDB shines -- it has a seq_0_to_1000 pseudo-table from which you can easily build a list of dates.)

The LEFT JOIN will provide NULLs for days with missing info. Then...

Here are two ways to turn NULL into 0 (there are others):

mysql> SELECT IFNULL(x, 0), COALESCE(x, 0)  FROM (SELECT null AS x UNION SELECT 123) z;
+--------------+----------------+
| IFNULL(x, 0) | COALESCE(x, 0) |
+--------------+----------------+
|            0 |              0 |
|          123 |            123 |
+--------------+----------------+

In your case, COUNT(*) provides 0; don't use COUNT(day) unless you want NULL. In general COUNT(*) counts the number of rows, while COUNT(foo) counts the number of rows WHERE foo IS NOT NULL. However, this is an exception; see AndriyM's Comment.