3

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?

  • 2
    you need to use a calendar table and then do a LEFT JOIN – Lamak Feb 8 '16 at 13:32
  • 1
    That column needs to be DATE, not a varchar(10). – ypercubeᵀᴹ Feb 8 '16 at 13:55
5

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 (10*10*10) 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
| improve this answer | |
  • One thing yet please. What form of select will when I need restrict, that I need get only records with "0" values for some sensor from my database, for example: SELECT timestamp, COUNT(*) FROM Tabs WHERE Tabs.sensor = "10" GROUP BY timestamp If I use it, it does not show values with "0"... – user41060 Feb 9 '16 at 10:46
  • I am not sure I understand. Do you need Group By ... Having count() = 10 ? – Julien Vavasseur Feb 9 '16 at 10:47
  • No, I need apply this select only for records that satisfy the condition that their Tabs.sensor ID is 10. After I need apply the same condition for records that is ID (Tabs.sensor) for example 5 and etc. Sensor is column in table Tabs that identify my sensors. And I need show records with 0 only for some these sensors (for example for sensor that Tabs.sensor = "10")... – user41060 Feb 9 '16 at 11:59
  • 1
    I move Tabs t to a subquery where a where clause can be added. another option without a subquery is sensor=10 or sensor is null – Julien Vavasseur Feb 9 '16 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.