How to show 0 when no data

Question

I want to show 0 or something i want when no data.And this is my query.

SELECT `icDate`,IFNULL(SUM(`icCost`),0) AS icCost 
FROM `incomp` 
WHERE (`icDate` BETWEEN "2016-01-01" AND "2016-01-05") 
  AND `compID` = "DDY" 
GROUP BY `icDate`

And this is result of this query.

   icDate   |   icCost  
--------------------------
 2016-01-01 |   1000.00
 2016-01-02 |   2000.00
 2016-01-03 |   3000.00
 2016-01-04 |   4000.00
 2016-01-05 |   5000.00

If every day i want to show data it have a data,It wasn't problem.But it have some day,It don't have data. This will not show this day, Like this.

   icDate   |   icCost  
--------------------------
 2016-01-01 |   1000.00
 2016-01-02 |   2000.00
 2016-01-04 |   4000.00
 2016-01-05 |   5000.00

But i want it can show data like this.

   icDate   |   icCost  
--------------------------
 2016-01-01 |   1000.00
 2016-01-02 |   2000.00
 2016-01-03 |      0.00
 2016-01-04 |   4000.00
 2016-01-05 |   5000.00

How to write query to get this answer.Thank you.

No promises, but it sounds like a CTE might be of some help here — allie, Feb 25 '16 at 16:09
Not quite a duplicate, but the following may be useful: http://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query/9296238#9296238 — , Feb 25 '16 at 16:16
As jarlh has pointed out, an outer join is indeed a good way to do this, but mysql doesn't have outer joins. You can do some hackery with "union" instead. Or switch to postgresql, a harder to use but far more featureful and complete and (assuming more than a trivial amount of data and query complexity) faster performing free database. — Peter, Feb 25 '16 at 16:19
@Peter: mysql's SQL is not very advanced, but supports Outer Joins for many years. — dnoeth, Feb 25 '16 at 16:28
@dnoeth heh yeah you're right... the 5.7 manual has outer joins. I even tried looking it up before commenting, and only found union hacks along with complaints about why they don't work and alternative suggestions, etc. like it's 1999 all over again. — Peter, Feb 25 '16 at 16:31

score 0 · Answer 1 · edited Feb 25 '16 at 18:09

I made a simulation but I could not see your problem. I created a table for teste and after insert data this was my select. But the test was normal!

SELECT icDate, 
format(ifnull(sum(icCost), 0),2) as icCost, 
count(icDate) as entries
FROM incomp
WHERE icDate BETWEEN '2016-01-01' AND '2016-01-05' 
AND compID = 'DDY' 
group by icDate;

This is result of my test, exported in csv file:

icDate      |  icCost   |  entries
----------------------------------
2016-01-01  |  8,600.00 |  8    
2016-01-02  |  5,600.00 |  4    
2016-01-03  |  5,400.00 |  3    
2016-01-04  |  0.00     |  1    
2016-01-05  |  7,050.00 |  7

Does the icCost field is setting with null value or number zero? Remember some cases that null values setted may be different from other one as empty.

I think the problem is that there are days where there is no record at all (not just a null value in the icCost field), but he still wants a zero to show up for that day. — Jerrad, Feb 25 '16 at 18:12

score 0 · Answer 2 · answered Feb 25 '16 at 18:18

I found the answers, It worked with calendar table.

SELECT tbd.`db_date`,
    (SELECT IFNULL(SUM(icCost),0) AS icCost
     FROM `incomp`
     WHERE icDate = tbd.db_date
       AND compID = "DDY"
     )AS icCost
FROM tb_date AS tbd
WHERE (tbd.`db_date` BETWEEN "2016-01-01" AND "2016-01-05") 
GROUP BY tbd.`db_date`
LIMIT 0,100

Simply, But work.

score 0 · Answer 3 · answered Feb 25 '16 at 20:10

Ok, you can investigate if you table is filled correctly every day. First you can create a temporary table like this:

CREATE TEMPORARY TABLE myCalendar (
    CalendarDate date primary key not null
);

So, after you need to fill this table with valid days. For it, use this procedure:

DELIMITER $$
CREATE PROCEDURE doWhile()

BEGIN

   # IF YOU WANT TO USE CURRENT MONTH
   #SET @startCount = ADDDATE(LAST_DAY(SUBDATE(CURDATE(), INTERVAL 1 MONTH)), 1);
   #SET @endCount = LAST_DAY(sysdate());
   # USE TO SET A DATE
   SET @startCount = '2016-01-01';
   SET @endOfCount = '2016-01-30';

  WHILE @startCount <= @endOfCount DO

    INSERT INTO myCalendar (CalendarDate) VALUES (@startCount);
    SET @startCount = date_add(@startCount, interval 1 day);

  END WHILE;

END$$;

DELIMITER ;

You need to run this procedure by command:

CALL doWhile();

Now, run the follow:

    SELECT format(ifnull(sum(t1.icCost), 0),2) as icCost,
           ifnull(t1.icDate, 'Not found') as icDate,
           t2.CalendarDate as 'For the day'
   from incomp t1
          right join myCalendar t2 ON
   t2.CalendarDate = t1.icDate  group by t2.CalendarDate;

I think this will help you to find a solution, for example, if exists a register for a day or not.

I hope this can help you!

[]'s

score -1 · Answer 4 · edited May 23 '17 at 11:50

Sorry for my earlier answer. I gave a MSSQL answer instead of a MySQL answer.

You need a calendar table to have a set of all dates in your range. This could be a permanent table or a temporary table. Either way, there are a number of ways to populate it. Here is one way (borrowed from here):

set @beginDate = '2016-01-01';
set @endDate = '2016-01-05';

create table DateSequence(Date Date);
insert into DateSequence
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 @beginDate and @endDate

Your best bet is probably to make a permanent table that has every possible date. That way you only have to populate it once and it's ready to go whenever you need it.

Now you can outer join the calendar table with your inComp table.

set @beginDate date = '2016-01-01'
set @endDate date = '2016-01-05'

select d.Date,
sum(ifnull(i.icCost, 0)) inComp
from DateSequence d
left outer join inComp i on i.icDate = d.Date
where d.Date between @beginDate and @endDate
and i.compID = 'DDY'
group by d.date
order by d.Date;

Sorry for the mixup. I've updated my answer. I shouldn't try to answer questions while I'm doing 10 other things. — Jerrad, Feb 25 '16 at 17:19

How to show 0 when no data

4 Answers4