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 the data array of the format [2012-08] => 4252.00 In the $start and $end unix variables of the beginning of August 2011 and the end of August 2012, respectively. If there are no records in the database with a date of some month, then the key with this date is naturally missing, 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
  • check the year, if (YEAR (dt)> 0, dt, '0') as 'god' - Vfvtnjd
  • @Vfvtnjd, and an example is possible? And I do not need to replace the year with 0, I need the format [year-month] to remain in the required interval, even if there are no records in the database for this month - DemoS

1 answer 1

Use the IFNULL () function

  • And an example based on my request? I know what IFNULL is, but I couldn’t screw it to the request in the right way - DemoS
  • one
    Request: SELECT IFNULL (SUM ( total ), 0) 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 - out
  • 2
    That the request returned date there should be - at least empty, differently from air itself will not draw. Empty dates, you need to check and finish at the code level already. - Fucking Babai
  • one
    If there is no date in the table, then it will not appear in the query results. You must either add dates to the table (without values), or substitute from a script (for example, in a loop), as already mentioned @ Yoharny Babai and @Vfvtnjd. - out
  • one
    Create an auxiliary table with a full set of the required months and LEFT (or RIGHT) JOIN with your query. - alexlz