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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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');

Answer 5

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