Displaying null fields as 0 in mySQL select 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.
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&gt; 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.