0

I'm trying to join 2 tables to know how many sales were made per 30min intervals. Problem : I'm having trouble to display intervals were sales are 0.

Here's what I got for now :

TABLE timeslice +----+----------+--------+ | ID | FromHour | ToHour | +----+----------+--------+ | 1 | 00:00 | 00:30 | | 2 | 00:30 | 01:00 | | 3 | 01:00 | 01:30 | | . | ... | ... | | 48 | 23:30 | 23:59 | +----+----------+--------+

TABLE sales +----+---------------------+----------+ | ID | dateSale | nb_sales | +----+---------------------+----------+ | 1 | 2013-12-01 05:30:25 | 3 | | 2 | 2013-12-01 06:12:00 | 1 | | 3 | 2013-12-01 06:15:17 | 2 | +----+---------------------+----------+

Query

SELECT DATE(dateSale) as dateSale, timeslice.FromHour, timeslice.ToHour, SUM(nb_sales) as nb_sales 
FROM timeslice
LEFT OUTER JOIN sales 
ON dateSale BETWEEN CONCAT(date(Sale),' ', FromHour) AND CONCAT(date(dateSale),' ',ToHour)
WHERE dateSale = '2013-12-01'
GROUP BY FromHour, DATE(dateSale)
ORDER BY FromHour, DATE(dateSale)

Output Query:

+-------------+----------+--------+----------+--+
|  dateSale   | FromHour | ToHour | nb_sales |  |
+-------------+----------+--------+----------+--+
| 2013-12-01  | 05:30    | 06:00  |        3 |  |
| 2013-12-01  | 06:00    | 06:30  |        3 |  |
+-------------+----------+--------+----------+--+

My problem is that I want 48 lines with 0 as nb_sales if that's the case. Any ideas ?

Edit :

With following query I'm getting closer of what I want :

Query

SELECT DATE(dateSale) as dateSale, timeslice.FromHour, timeslice.ToHour, SUM(nb_sales) as nb_sales 
FROM timeslice
LEFT OUTER JOIN sales 
ON dateSale BETWEEN CONCAT(date(Sale),' ', FromHour) AND CONCAT(date(dateSale),' ',ToHour)
AND dateSale = '2013-12-01'
GROUP BY FromHour, DATE(dateSale)
ORDER BY DATE(dateSale), FromHour

Output Query:

+-------------+----------+--------+----------+--+
|  dateSale   | FromHour | ToHour | nb_sales |  |
+-------------+----------+--------+----------+--+
| ...         | ...      | ...    | ...      |  |
| NULL        | 05:00    | 05:30  | NULL     |  |
| 2013-12-01  | 05:30    | 06:00  | 3        |  |
| 2013-12-01  | 06:00    | 06:30  | 3        |  |
| ...         | ...      | ...    | ...      |  |
+-------------+----------+--------+----------+--+

How can I have the date instead of null? I don't even know if this is possible since the date is only in the sales Table and i'm doing a left join..

caRameL
  • 633
  • 6
  • 15
  • A left join with where condition is inner join, you may move the where condition as one of joining condition. – Abhik Chakraborty Dec 12 '14 at 10:37
  • Yes. Change WHERE to AND. Note that that specific ORDER BY clause is redundant here – Strawberry Dec 12 '14 at 10:38
  • Thanks for this answer, however when I do this, the date also shows as NULL and i'm not able to determine which day sales were at 0 (if I use a range of date). But solution is close :) – caRameL Dec 12 '14 at 10:42
  • Yes the dates will be shown as null since the left join may not find any dates, in this case you may need to generate the dynamic dates and do a left join here is one similar answer I gave http://stackoverflow.com/questions/27228991/create-a-by-date-and-hour-breakdown-from-timestamp-field-mysql/27233501#27233501 you may check this. – Abhik Chakraborty Dec 12 '14 at 11:23

0 Answers0