MySQL: How to get records even when no record on the date?

Question

I am getting number of visits every day for generating a chart. Even when there are zero records, I want to get the record with count 0.

I am planning to create a table which will contain every day, and when fetching - data will join with this table and get count of the records from visit table. Is there any other way to do the same in mySQL?

Visit Table with Sample Data

Date       | ........  
----------------------
01/11/2014 | --------
03/11/2014 | --------

I want results even for 02/11/2014 with count 0. If I group by date - I will get count only when records exists on a particular date.

Why cant the code that calls this db or shows on screen show 0 or default information when there is no record? — tgkprog, Nov 26 '14 at 17:35
I want to have record with 0 count for each date even when there is no record. So when I group by a date column will not get record when no record on the date. — Nish, Nov 26 '14 at 17:37
@tgkprog I am getting the record in an array and passing it directly to a charting library and don't want to manipulate the data in JavaScript. — Nish, Nov 26 '14 at 17:39
you need to generate a date table see here http://stackoverflow.com/questions/10132024/how-to-populate-a-table-with-a-range-of-dates and then left join your table to this table. — radar, Nov 26 '14 at 17:46
check this it one of my previous answer might help you http://stackoverflow.com/questions/23300303/mysql-single-table-select-last-7-days-and-include-empty-rows/23301236#23301236 — Abhik Chakraborty, Nov 26 '14 at 18:09

score 0 · Answer 1 · answered Nov 09 '17 at 18:02

I'll try to read in between lines of your question... Sort of game where I write the question and the answer :-/

You have a table (my_stats) holding two fields, one is the date (my_date) the other is a integer (my_counter).

By some mean, you will need a table holding a list of all dates you want to use in your output. This could be done with a temp table... (but not all hosting solution will allow you this) the other is to build it up on the fly, using a view or a stored procedure. Then you will LEFT JOIN this table/view/stored procedure/etc... to your table my_visits based on the date field. This will output you all dates, and when there won't be a match in mour my_visits you'll have a NULL value. ( IFNULL(my_visits.my_counter, 0) will give you a 0 (zero) when there is no matching value.

inspiration: Get a list of dates between two dates + How to get list of dates between two dates in mysql select query and a nice solution here that needs no loops, procedures, or temp tables generate days from date range

Based on that last link, here we go...

first a sample table

DROP TABLE IF EXISTS `my_stats`;
CREATE TABLE IF NOT EXISTS `my_stats` (
  `my_date` date NOT NULL,
  `my_counter` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

INSERT INTO `my_stats` (`my_date`, `my_counter`) VALUES
('2017-11-01', 2),
('2017-11-02', 3),
('2017-11-03', 5),
('2017-11-05', 3),
('2017-11-07', 7);

And now a working exemple BETWEEN '2017-11-01' AND '2017-11-09'

SELECT date_range.date                AS the_date, 
       IFNULL(my_stats.my_counter, 0) AS the_counter 
FROM (
    SELECT a.date 
    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 
    WHERE  a.date BETWEEN '2017-11-01' AND '2017-11-09'
    ) AS date_range 
   LEFT JOIN my_stats 
          ON( date_range.date = my_stats.my_date ) 
ORDER  BY the_date ASC

Output

+------------+-------------+
| the_date   | the_counter |
+------------+-------------+
| 2017-11-01 |           2 |
| 2017-11-02 |           3 |
| 2017-11-03 |           5 |
| 2017-11-04 |           0 |
| 2017-11-05 |           3 |
| 2017-11-06 |           0 |
| 2017-11-07 |           7 |
| 2017-11-08 |           0 |
| 2017-11-09 |           0 |
+------------+-------------+

MySQL: How to get records even when no record on the date?

Visit Table with Sample Data

1 Answers1