0 When no row exist

Question

I have following sql query

 SELECT DATE_FORMAT(date,'%d.%m. %y') as date, COUNT(idStat) as number
  FROM stat 
  WHERE IdGame = ? AND date >= ? AND date <= ? 
  GROUP BY date

It returns date and how much people visited game this day, but how to return 0, when no rows exist this day?

For example day 15.12.1993 does not exist in db, but user pick date between 15.10.1950 and 12.15.2020.

I want to return this non existing date 15.12.1993 but with count 0. Is this even possible?

Thanks for help,

Filip.

You would have to `left join` against a table that contains all dates. But you should handle that in your program logic and not in SQL — juergen d, May 13 '16 at 10:56
Or, if you don't have a table with all dates, you can generate them using a recursive query. — , May 13 '16 at 10:56
but date in same table... In table is date column, city column, ip address column... — Raiper34, May 13 '16 at 10:57
Have a look at http://stackoverflow.com/questions/2157282/generate-days-from-date-range a **LEFT JOIN** would help. — Sougata Bose, May 13 '16 at 11:05

score 5 · Answer 1 · answered May 13 '16 at 10:58

The best way is to have a Calendar table handy with relevant dates. You can then use a left join to get the dates. Something like this:

SELECT DATE_FORMAT(c.date,'%d.%m. %y') as date, COUNT(s.idStat) as number
FROM Calendar c LEFT JOIN
     stat s
     ON c.date = s.date AND s.IdGame = ?
WHERE c.date >= ? AND c.date <= ?
GROUP BY c.date;

If you have games on every date but the problem is that the particular game is not one the day, you can use this short-cut:

SELECT DATE_FORMAT(date,'%d.%m. %y') as date, SUM(IdGame = ?) as number
FROM stat
WHERE date >= ? AND date <= ?
GROUP BY date;

This doesn't work in all cases, but it can be a useful short-cut.

I can not add Calendar table :/ I can work only with existing tables. And if my game have not got visit at specific date, like 15.12.1993, there is no row for it. — Raiper34, May 13 '16 at 11:02

score 0 · Accepted Answer · edited May 23 '17 at 11:52

I used RedFilter Answer to solve my problem, from this link: generate days from date range

My query now looks like this:

select DATE_FORMAT(a.Date,'%d.%m. %Y'), COUNT(stat.IdStat)
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) as a LEFT JOIN stat ON a.Date = stat.date
where a.Date between ? and ? AND (stat.IdGame = ? OR stat.IdGame IS NULL) GROUP BY a.Date

But I need remove future dates form my datapicker, because when i use futue date in this sql, no data will be return... I need set min and max of my datapicker.

0 When no row exist

2 Answers2