Fill missing values with 0 - sql query

Question

I have an SQL query as outlined below that selects data between two dates.

SELECT date, total FROM db WHERE date >= '2016-03-14' AND date <= '2016-03-20';

I'd like to output a "0" where there's no data for various dates, for example:

Query spans = 2016-03-14 to 2016-03-20

Currently my SQL would output:

Date         Total
2016-03-14   50

I'd like to output:

Date        Total
2016-03-14  50
2016-03-15  0
2016-03-16  0
2016-03-17  0
2016-03-18  0
2016-03-19  0
2016-03-20  0

Is there any way to do this without complex joins?

Thanks,

Matt

You need to have some generated dates prior to do that. And of course you need to adopt join for that. — 1000111, Mar 15 '16 at 04:39
Generally, problems of data display are best handled in the presentation layer, if that's available (e.g. a simple PHP loop) — Strawberry, Mar 15 '16 at 04:58
You need to have a **rowsource** for the dates that includes dates that aren't in your `db` table. And a simple outer join to your `db` table. There are lots of different ways to get the dates. Simplest to use is a calendar table. But there are lots of other ways to get a list of dates. — spencer7593, Mar 15 '16 at 05:00

score 1 · Answer 1 · answered Mar 15 '16 at 04:41

The best way to create records for dates that don't exist in your data is to join to a calendar table.

SELECT a.cal_dt, COALESCE(b.total,0) AS total
FROM lkp_Calendar a
LEFT JOIN db b
  ON b.date = a.cal_dt 
WHERE a.cal_dt >= '2016-03-14' 
  AND a.cal_dt <= '2016-03-20';

There are many good scripts out there to create robust calendar tables, a simple one is:

CREATE TABLE lkp_Calendar (cal_dt date);

CREATE PROCEDURE addDates(dateStart DATE, dateEnd DATE)
BEGIN
      WHILE dateStart <= dateEnd DO
        INSERT INTO lkp_Calendar (cal_dt) VALUES (dateStart);
        SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
      END WHILE;
    END;

CALL addDates('2016-01-01','2016-12-31');

score 0 · Answer 2 · answered Mar 15 '16 at 04:41

0

SELECT date, COUNT(*) AS total FROM db WHERE date >= '2016-03-14' AND date <= '2016-03-20' GROUP BY date;

I assume:

date is just a date (has no time part)
total IS NOT a column, just a register count

answered Mar 15 '16 at 04:41

Ing. Gerardo Sánchez

1,607
15
14

score 0 · Answer 3 · answered Mar 15 '16 at 04:45

It doesn't require complex joins. But it does require a rowsource for the missing date values you want returned.

One option is to use a calendar table populated with dates.

create table cal (dt DATE NOT NULL PRIMARY KEY) ... ;
insert into cal (dt) values ('2016-03-01');
insert into cal (dt) select dt + interval 1 day from cal order by dt;
insert into cal (dt) select dt + interval 2 day from cal order by dt;
insert into cal (dt) select dt + interval 4 day from cal order by dt;
insert into cal (dt) select dt + interval 8 day from cal order by dt;
insert into cal (dt) select dt + interval 16 day from cal order by dt;

Then pull the dates from that:

 SELECT c.dt
   FROM cal c
  WHERE c.dt >= '2016-03-14'
    AND c.dt <  '2016-03-21'

Then just do the simple outer join to your table:

 SELECT c.dt              AS `date` 
      , IFNULL(d.total,0) AS `total`
   FROM cal c
   LEFT
   JOIN db d
     ON d.date = c.dt
  WHERE c.dt >= '2016-03-14'
    AND c.dt <  '2016-03-21'
  ORDER BY c.dt

If you don't have a calendar table, you can use an inline view that does UNION ALL

 SELECT c.dt              AS `date` 
      , IFNULL(d.total,0) AS `total`
   FROM ( SELECT '2016-03-14' + INTERVAL 0 DAY AS dt
          UNION ALL SELECT '2016-03-15' + INTERVAL 0 DAY 
          UNION ALL SELECT '2016-03-16' + INTERVAL 0 DAY 
          UNION ALL SELECT '2016-03-17' + INTERVAL 0 DAY 
          UNION ALL SELECT '2016-03-18' + INTERVAL 0 DAY 
          UNION ALL SELECT '2016-03-19' + INTERVAL 0 DAY 
          UNION ALL SELECT '2016-03-20' + INTERVAL 0 DAY 
        ) c
   LEFT
   JOIN db d
     ON d.date = c.dt
  ORDER BY c.dt

score 0 · Answer 4 · answered Mar 15 '16 at 04:49

0

You can also try the below query. This caters for the scenario where you don't have records for all the dates in the underlying table.

DECLARE @temp TABLE (dbdate DATE, total INT)

DECLARE @StartDate DATE = '2016-03-14'
DECLARE @EndDate DATE = '2016-03-20'

WHILE (@StartDate <= @EndDate)
BEGIN
    INSERT @temp
    SELECT @StartDate AS [dbDate], ISNULL((SELECT total FROM db WHERE [date] = @StartDate),0) AS Total
    SET @StartDate = DATEADD(dd,1,@StartDate)
END

SELECT * FROM @temp

answered Mar 15 '16 at 04:49

Amit Sukralia

950
1
5
13

This looks like SQL Server syntax. Not MySQL. – spencer7593 Mar 15 '16 at 04:50
Yes, this is SQL Server syntax. I thought the tag mentions sql as well in addition to mysql. – Amit Sukralia Mar 15 '16 at 05:02

score 0 · Answer 5 · answered Mar 15 '16 at 04:50

Try this (The fiddle demo is slightly different as I didn't have data of db table)

SQLFiddle Demo

select selected_date,coalesce(count1,0) as count1 from 
    (select * from 
    (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
     (select 0 i 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 i 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 i 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 i 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 i 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 selected_date between '2016-03-14' and '2016-03-20'
    ) t
left join 
    (SELECT dt, count1 FROM db WHERE dt between '2016-03-14' and '2016-03-20') t1
on t.selected_date=t1.dt

Fill missing values with 0 - sql query

5 Answers5