How to show rows with 0 without any records in database

I have a lot of record in my database and for data analysis, I need to show count of records for each day including days when I do not have any record, so result is 0.

For example I have these records (SQL Fiddle):

CREATE TABLE Tabs
    (`timestamp` varchar(10), `id` int)
;

INSERT INTO Tabs
    (`timestamp`, `id`)
VALUES
    ('2015-01-01', 1),
    ('2015-01-01', 2),
    ('2015-01-02', 3),
    ('2015-01-02', 4),
    ('2015-01-02', 5),
    ('2015-01-04', 6),
    ('2015-01-05', 7),
    ('2015-01-05', 8)
;

When I use this query:

SELECT `timestamp`, COUNT(*)
FROM Tabs
GROUP BY `timestamp`

I get ths result:

timestamp   COUNT(*)
2015-01-01  2
2015-01-02  3
2015-01-04  1
2015-01-05  2

But I need 2015-01-03 too with 0, so correct output for me is:

timestamp   COUNT(*)
2015-01-01  2
2015-01-02  3
2015-01-02  0
2015-01-04  1
2015-01-05  2

How can I realize it please?

I suppose you know the date_start and date_end and you won't work on more than 1000 days although it can be extended.

This example works with these values:

SET @date_start := CAST('20150101' as date);
SET @date_end := CAST('20150108' as date);

Below queries return each day between @date_start (2015-01-01) and @date_end(2015-01-08). This is similar to a calendar. The full list of days can then be LEFT JOIN to the list of available days in the table.

Using SELECT ... UNION ALL ...

SELECT @date := date_add(@date, INTERVAL 1 DAY) as date
FROM (
    SELECT 0 as n 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
  )d10, (
    SELECT 0 as n 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   
  )d100, (
    SELECT 0 as n 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   
  ) d1000
  , (SELECT @date := date_add(@date_start, INTERVAL -1 DAY)) v
WHERE @date < @date_end
;

Output:

date
2015-01-01
2015-01-02
2015-01-03
2015-01-04
2015-01-05
2015-01-06
2015-01-07
2015-01-08

• Each inner SELECT ... UNION ALL ... returns 10 rows from 0 to 9

• This SELECT is used and CROSS JOIN 3 times and returns 1000 rows (101010) from day 0 to day 999

• Because 1 is added to @date, it means that it can return up to 1000 consecutives days

• You can work on 10.000 days if you add one more SELECT ... UNION ALL ... and so on or on 100 if the third join is removed

• You can then LEFT JOIN this query with your table

Using a view

It is probably easier to create a view with numbers from 0 to 9 and CROSS JOIN it 3 times or create 1 single view from 0 to 999:

CREATE VIEW viewDec As
  SELECT 0 as n 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
;    

SELECT @date := date_add(@date, INTERVAL 1 DAY) as date
FROM viewDec d10, viewDec d100, viewDec d1000
    , (SELECT @date := date_add(@date_start, INTERVAL -1 DAY)) v
WHERE @date < @date_end
;

• You can work on 10.000 days by adding an extra viewDec d10000 ...

Using an existing table

It is also possible to replace the SELECT or VIEW by an existing table with enough rows (1000 in this example):

SELECT @date := date_add(@date, INTERVAL 1 DAY) as date
FROM (SELECT 1 as n FROM table_with_over_1000_rows LIMIT 1000) t
    , (SELECT @date := date_add(@date_start, INTERVAL -1 DAY)) v
WHERE @date < @date_end
;

Full query (using viewDec)

SELECT `date`, count(t.`id`)
FROM (
  SELECT @date := date_add(@date, INTERVAL 1 DAY) as date
  FROM viewDec d10, viewDec d100, viewDec d1000
    , (SELECT @date := date_add(@date_start, INTERVAL -1 DAY)) v
  WHERE @date < @date_end  
) d
LEFT JOIN (
    SELECT `id`, `timestamp`
    FROM Tabs
    /* WHERE sensor = 10 */
) t
    ON d.`date` = t.`timestamp`
GROUP BY d.`date`
;

In this SQL Fiddle timestamp has been replaced by a type date column.

Output:

date        count(t.`id`)
2015-01-01  2
2015-01-02  3
2015-01-03  0
2015-01-04  1
2015-01-05  2
2015-01-06  0
2015-01-07  0
2015-01-08  0

StackExchange Database Administrators Q#128600, answer score: 5