inquiry – How to rebuild the query so that the keys are there even when the data is missing?


There is such a request

SELECT SUM(`total`) AS `sum`,CONCAT_WS('-',year(FROM_UNIXTIME(`unix`)),DATE_FORMAT(FROM_UNIXTIME(`unix`),'%m')) AS `key` FROM `shop_orders` WHERE `status` NOT IN (1,6) AND `unix` BETWEEN ".(int)$start." AND ".(int)$end." GROUP BY `key`

It returns an array of data of the format [2012-08] => 4252.00 In the $start and $end variables of the unix dates of the beginning of August 2011 and the end of August 2012, respectively. If there are no records in the database with the date of any month, then the key with this date is naturally absent, this is logical.

Question: is it possible to rebuild the query in such a way that the keys are in any case, and if there is no data, then the value was 0?

Example (in the absence of data in the database for April and June):

[2012-04] => 0
[2012-05] => 152.00
[2012-06] => 0
[2012-07] => 5721.00
[2012-08] => 4252.00


Use the IFNULL () function

Scroll to Top