CODEWITHSUNDEEP
mysql
Errol Fitzgerald
Errol Fitzgerald

Reputation: 3008

MySQL Group By Date for NULL values

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);

Upvotes: 2

Views: 5345

Answers (2)

user359135
user359135

Reputation:

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

Upvotes: 2

nobody
nobody

Reputation: 10645

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.

Upvotes: 0

Related Questions