MySQL Group By Date for NULL values

Question

I am trying to return the number of orders for every day a commerce site has been live, but for days where there were 0 orders my sql statement returns null, and hence their is a gap in the data. How can I insert a 0 into these days so the data is complete?

    $sql = "SELECT COUNT(*) AS orders
        FROM `orders`
        GROUP BY DATE(`order_datetime`)
        ORDER BY DATE(`order_time`) ASC";

    $query = $this->db->query($sql);

I have tried using ifnull like so but I get the same result as above:

    $sql = "SELECT ifnull(COUNT(*),0) AS orders
        FROM `orders`
        GROUP BY DATE(`order_datetime`)
        ORDER BY DATE(`order_time`) ASC";

    $query = $this->db->query($sql);

related http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range — Haim Evgi, Sep 14 '11 at 09:42

score 2 · Accepted Answer · edited May 23 '17 at 10:24

can you use COALESCE?

eg:

SELECT COALESCE(COUNT(*),0) AS orders
FROM `orders`
GROUP BY DATE(`order_datetime`)
ORDER BY DATE(`order_time`) ASC

it returns first non null value in the list - count if its not null and 0 if it is.

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

hang on.. is the issue that there are no rows with those dates so you just get results for those dates?

if so you could return the order date too and be a bit more clever about how you display your result:

SELECT  order_datetime,COUNT(*) AS orders
FROM `orders`
GROUP BY DATE(`order_datetime`)
ORDER BY DATE(`order_time`) ASC

then when outputting your results maybe create a loop that iterates through the dates you are interested in and prints a 0 if there is no row for that date:

for (datetime  d = startdate; d <= enddate; datetime.adddays(1)){
    if (recordset[order_datetime] == d){
        output (d,recordset[orders]);
        recordset.movenext();
    }else
    {
        output (d,0);
    }
}

(note i have no idea what programming language that would be ;-)

if you want to do it all in the DB then look at this SO question for how to create a table of the dates on the fly and then you can left outer join from it to you query on date..

Get a list of dates between two dates

not tried coalesce for this specifically so don't know exactly how it will behave here — , Sep 14 '11 at 09:42
or if you have all the dates you are interested in in another table you could left outer join from it to your query.. or you could create that table on the fly and join to it.. but why bother? — , Sep 14 '11 at 09:58
ya i thinks it a lot easier to handle the missing dates outside sql — Errol Fitzgerald, Sep 14 '11 at 10:13

score 0 · Answer 2 · answered Sep 14 '11 at 09:51

0

You should select all the dates you want manually in a sub-query and LEFT JOIN it with your table:

SELECT COUNT(*) AS orders FROM
  ( SELECT '2010-09-01' AS date UNION SELECT '2010-09-02' UNION
    SELECT '2010-09-03' UNION SELECT '2010-09-04' UNION SELECT '2010-09-05' UNION
    SELECT '2010-09-06' UNION SELECT '2010-09-07' ... ) AS dates
  LEFT JOIN orders ON( dates.date = orders.order_datetime )
  GROUP BY orders.order_datetime
  ORDER BY orders.order_datetime

or use you current query and fill the gaps in your php program after receiving the results.

answered Sep 14 '11 at 09:51

nobody

10,599
4
26
43

ya this a pretty solid way to handle this, but I think it's just easier to handle the missing dates outside sql. Thanks for the help! – Errol Fitzgerald Sep 14 '11 at 10:12
how to left join this one for multiple id ??? so the same date will display two data with different id ??? – Anthony Kal Mar 03 '18 at 08:19

MySQL Group By Date for NULL values

2 Answers2

Linked