MYSQL - How do I show 0 is there are no results?

Question

I have the below query which is working fine and shows any missed calls on a dashboard. However if there are no results it returns no data. How do I show '0' is there are no results? Thanks

SELECT DialledNumber, COUNT(*) As Missed
FROM CALLS
WHERE destination like '%!%' AND datetime BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 day)
GROUP By DialledNumber
HAVING (DialledNumber = '500') OR (DialledNumber = '580') OR (DialledNumber = '515') OR (DialledNumber = '513') OR (DialledNumber = '514')

Put your HAVING conditions in a WHERE clause instead, having is for aggregate function conditions. — jarlh, Feb 06 '15 at 11:03
SQL can't show data that isn't there. See question 11337905 for a similar problem with enumerating dates that have no data. — Neil, Feb 06 '15 at 11:10
possible duplicate of [Selecting Non-existent Data With MySQL](http://stackoverflow.com/questions/11337905/selecting-non-existent-data-with-mysql) — Neil, Feb 06 '15 at 11:10

score 0 · Answer 1 · answered Feb 06 '15 at 10:58

0

You can use IFNULL function in MYSQL

SELECT DialledNumber, IFNULL(COUNT(DialledNumber),0) As Missed
FROM CALLS
WHERE destination like '%!%' AND datetime BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 day)
GROUP By DialledNumber
HAVING (DialledNumber = '500') OR (DialledNumber = '580') OR (DialledNumber = '515') OR (DialledNumber = '513') OR (DialledNumber = '514')

answered Feb 06 '15 at 10:58

Dgan

10,077
1
29
51

Thanks, I tried the above but it returned 'No Data Found' message? – AM340 Feb 06 '15 at 11:17

score 0 · Answer 2 · answered Feb 06 '15 at 11:34

You want to use left join rather than in:

SELECT dn.dn, COUNT(c.diallednumber) As Missed
FROM (select '500' as dn union all select '580' union all select '515' union all
      select '513' union all select '514'
     ) dn left join
     CALLS c
     ON c.diallednumber = dn.dn and
        c.destination like '%!%' AND
        c.datetime BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 day)
GROUP By dn.dn;

As a small note, in English the word "dialed" does not have two "l"s.

Thank you I will give this a go. Haha I know I will tell the supplier of the database — AM340, Feb 06 '15 at 12:48

score 0 · Answer 3 · answered Feb 06 '15 at 11:49

This could work for you (untested):

SELECT CALLS.DialedNumber, IFNULL(MISSEDCALLS.Missed, 0) FROM CALLS
LEFT JOIN (
  SELECT DialedNumber, COUNT(*) As Missed FROM CALLS
  WHERE destination like '%!%' AND datetime BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 day) 
  GROUP By DialledNumber
) AS MISSEDCALLS ON MISSEDCALLS.DialedNumber = CALLS.DialedNumber
WHERE CALLS.DialledNumber IN ('500', '580', '515', '513', '514')

MYSQL - How do I show 0 is there are no results?

3 Answers3