SQL return 0 if count == 0

Question

I want to get all counts between two dates in Mysql but, when no value exists in the specific date the SQL result return none. But I want to return 0.

    SELECT
    DATE(`date`) AS RegistrationDate,   COUNT(`id`) AS NumberOfRegistrations
 FROM
    Users
 WHERE
    `date` between "2018/05/1" and "2018/05/13"
 GROUP BY
    RegistrationDate

The answer depends on the database you’re using. Please add the tag of your database to your question. — Bohemian, May 13 '18 at 08:43
It'll be easier if you can provide sample data and expected data in tabular format. Pls read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/). — DxTx, May 13 '18 at 08:47
You could create a table with your range of dates - look [here](https://stackoverflow.com/questions/10132024/how-to-populate-a-table-with-a-range-of-dates) and join it to your table. This way you'll get all the dates in the range even if the count is 0 — Ofir Winegarten, May 13 '18 at 08:54

Ofir Winegarten · Accepted Answer · 2018-05-13T11:11:09.283

As in my comment here is my approach to your problem.
(there might be other ways)

The first table in the join creates you range of dates.

SELECT
  DATE(`gen_date`) AS RegistrationDate, 
  COUNT(`id`) AS NumberOfRegistrations 
FROM 
  (
    SELECT * 
    FROM
      (SELECT adddate('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) gen_date 
        FROM 
          (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, 
          (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)  t1, 
          (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)  t2,  
          (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)  t3, 
          (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)  t4
      ) v 
    WHERE 
      gen_date BETWEEN "2018-05-01" AND "2018-05-13"
  ) d 
LEFT OUTER JOIN users u ON Date(`gen_date`) = Date(`date`)
GROUP BY
  gen_date

See it working here, Updated

yes i know but my date have hour, how to add in the request all day ? — Eclixal, May 13 '18 at 09:26

score 0 · Answer 2 · answered May 13 '18 at 11:51

If you have a calendar table, then your problem is solved:

SELECT DATE(`date`) AS RegistrationDate, COUNT(u.id) AS NumberOfRegistrations
FROM Calendar c LEFT JOIN
     Users u
     ON DATE(u.date) = c.date
WHERE c.date >= '2018-05-01' AND c.date < '2018-05-14'
GROUP BY RegistrationDate;

A Calendar table can be quite useful and you can find one with a Google search.

You can do something similar with a numbers table, if you happen to have one handy.

Absent those, you can generate the dates you want:

SELECT DATE(`date`) AS RegistrationDate, COUNT(u.id) AS NumberOfRegistrations
FROM (SELECT DATE('2018-05-01') as dte UNION ALL
             DATE('2018-05-02') as dte UNION ALL
             DATE('2018-05-03') as dte UNION ALL
             DATE('2018-05-04') as dte UNION ALL
             DATE('2018-05-05') as dte UNION ALL
             DATE('2018-05-06') as dte UNION ALL
             DATE('2018-05-07') as dte UNION ALL
             DATE('2018-05-08') as dte UNION ALL
             DATE('2018-05-09') as dte UNION ALL
             DATE('2018-05-10') as dte UNION ALL
             DATE('2018-05-11') as dte UNION ALL
             DATE('2018-05-12') as dte UNION ALL
             DATE('2018-05-13') as dte UNION ALL
             DATE('2018-05-14') as dte
     ) c LEFT JOIN
     Users u
     ON DATE(u.date) = c.dte
WHERE c.dte >= '2018-05-01' AND c.dte < '2018-05-14'
GROUP BY RegistrationDate;

Notes:

Use standard date formats for dates YYYY-MM-DD.
The standard string delimiter in SQL is the single quote. Use it, unless you have a good reason for using double quotes.
Don't use between with dates. It is unclear whether you intend to include 2018-05-13, but your query will probably exclude any data on that date (due to the time component).

SQL return 0 if count == 0

2 Answers2