Sample Header Ad - 728x90

Displaying null fields as 0 in mySQL select

1 vote
1 answer
179 views
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.
Asked by Neil Lombardo (11 rep)
Jan 6, 2021, 01:03 PM
Last activity: Jan 8, 2021, 04:58 PM