0

Im trying to graph the sum of orders occuring for a particular date for the last 7 days but am having a problem when their aren't any orders for a particular date as the query isn't a value nor a date and therefore returning an array less than 7.

SELECT IFNULL(COUNT(*),0) as purchase_count, DATE(tb_order.order_date) as order_date
                FROM tb_order_attendee_info
                JOIN tb_order on tb_order.order_id = tb_order_attendee_info.order_id
                JOIN tb_events on tb_events.event_id = tb_order.event_id
                 WHERE tb_order_attendee_info.refunded='N'  AND order_date between DATE_SUB(now(), interval 10 day) and now()
                GROUP BY DATE(tb_order.order_date)";

Ive been trying to work out how i can get it to return the array with zero values for purchase count and the incremented date, unfortunately its only returning a date when the count is greater than 0.

Lawrence Cherone
  • 46,049
  • 7
  • 62
  • 106
Matt James
  • 182
  • 1
  • 1
  • 9

1 Answers1

1

You are trying to introduce result rows where there are none in the data. This is just not possible (without using a Stored Procedure). You cannot iterate over the dates as, so to say, there are no for loops in SQL.

The closest you can get is having/creating a (temporary) table of all the dates you want to have in the result and then JOIN to this table.

AndreKR
  • 32,613
  • 18
  • 106
  • 168