How do I use a GROUP BY, with a date range (i.e. a month) - MySQL

Asked Mar 12 '16 at 21:53

Active Mar 12 '16 at 22:55

Viewed 50 times

I am trying to create a query that displays the "total number of bagels sold" for a month and having the name of the bagel displayed next to it. I am also trying to include in the list the bagels that "weren't sold".

I have a table called Order I want to use the FOREIGN KEY I have in it called O_BagelID referenced from another table Bagel to count the number of times each item of Bagel appears in the Order table. By displaying the Bagel Name(Description), and counting the number of times a particular BagelID is presents.I also want to include the ones that do not appear at all.

So far I've tried doing something like this (which is clearly incorrect),

SELECT Bagel.Description,COUNT(`Order`.OrderID) AS 'Number Of Bagels Sold' FROM `Order`
LEFT JOIN Bagel
ON BagelID=Bagel.BagelID
GROUP BY Description WHERE Order_date BETWEEN '2016-2-1' AND '2016-2-29';

edited Mar 12 '16 at 22:55

alexander.polomodov

5,396
14
39
46

asked Mar 12 '16 at 21:53

Jian Li

WHERE before GROUP BY SELECT Bagel.Description,COUNT(`Order`.OrderID) AS 'Number Of Bagels Sold' FROM `Order` LEFT JOIN Bagel ON BagelID=Bagel.BagelID WHERE Order_date BETWEEN '2016-2-1' AND '2016-2-29' GROUP BY Description; – Bernd Buffen Mar 12 '16 at 21:56
1

^^ `WHERE` before `GROUP BY`, @BerndBuffen meant to say. – Michael Berkowski Mar 12 '16 at 21:57
@ Michael Berkowski - UPS - sorry – Bernd Buffen Mar 12 '16 at 21:59
on a side note you can just use `WHERE MONTH(Order_date) = 2` – But those new buttons though.. Mar 12 '16 at 22:13

How do I use a GROUP BY, with a date range (i.e. a month) - MySQL

0 Answers0