A.E A.E - 4 months ago 14
SQL Question

How to make query return 0 instead of empty set if there is no result

How can i make this query to return a row with 0 value if there is no value for each date

SELECT COUNT(id) FROM `panel_messages` WHERE `sent_by` = 'root'
AND `send_date` IN ("1395-4-25","1395-4-24","1395-4-23","1395-4-22","1395-4-21","1395-4-20","1395-4-19")
GROUP BY `send_date`
ORDER BY `send_date` DESC


My expected result is 7 rows like this :

| row1 |

| row2 |

| row3 |

| row4 |

| row5 |

| row6 |

| row7 |


and if there is no result for one of the rows i want it to be 0 which is default value :

| 2 |

| 0 |

| 0 |

| 2 |

| 0 |

| 3 |

| 1 |


But right now i just get 4 rows because if there is no result my query doesn't return anything :

| 2 |

| 2 |

| 3 |

| 1 |


SQL fiddle : http://sqlfiddle.com/#!9/a07486/3

Answer

try this :

SELECT sent_by ,"1395-4-25" as `SEND DATE`,COUNT(*)  FROM `panel_messages` WHERE `sent_by` = 'root' AND `send_date` = "1395-4-25"
 union 
SELECT sent_by ,"1395-4-24" as `SEND DATE`,COUNT(*) FROM `panel_messages` WHERE `sent_by` = 'root' AND `send_date` = "1395-4-24" 
union
SELECT sent_by ,"1395-4-23" as `SEND DATE`,COUNT(*) FROM `panel_messages` WHERE `sent_by` = 'root' AND `send_date` = "1395-4-23" 
ORDER BY  `SEND DATE` DESC

in this case when date is not found the count(*) return 0; but in the first return null add the 4 select statement and it will return 7 rows now it work but it can be better if i found onother solution i'm going back here

Comments