<[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > "Shaun" <[EMAIL PROTECTED]> wrote on 08/31/2005 04:04:20 PM: > >> >> <[EMAIL PROTECTED]> wrote in message >> > news:[EMAIL PROTECTED] >> > "Shaun" <[EMAIL PROTECTED]> wrote on 08/31/2005 02:43:33 PM: >> > >> >> Hi, >> >> >> >> This query counts the number of entries in the Bookings table per > month, >> > is >> >> it possible for this query to return a zero where there are no > entries >> > for a >> >> particular month? >> >> >> >> SELECT COUNT(Booking_ID) AS "Num_Bookings" >> >> FROM Bookings >> >> WHERE User_ID = 1 >> >> AND Work_Type_ID = 12 >> >> AND DATE_FORMAT(Booking_Start_Date, "%m-%Y") >= "04-2005" >> >> AND DATE_FORMAT(Booking_Start_Date, "%m-%Y") <= "06-2005" >> >> AND Booking_Type = "Booking" >> >> GROUP BY MONTH(Booking_Start_Date) >> >> ORDER BY Booking_Start_Date >> >> >> >> Thanks for your help. >> >> >> > >> > You cannot have missing month values in your query result unless > provide >> > values to fill the gaps with. The easiest way to do that is to create > a >> > simple lookup table. >> > >> > CREATE TABLE monthList ( >> > id int, >> > name varchar(18) not null >> > ) >> > >> > INSERT monthList (id, name) values (1,'January'),(2,'February'),... > fill >> > in the rest...,(12,'December'); >> > >> > Now you can have something to show even if your Bookings data doesn't. >> > Here is how you would use it in your sample query: >> > >> > SELECT m.name, COUNT(b.Booking_ID) AS "Num_Bookings" >> > FROM monthList m >> > LEFT JOIN Bookings b >> > ON m.id = MONTH(b.Booking_Start_Date) >> > WHERE b.User_ID = 1 >> > AND b.Work_Type_ID = 12 >> > AND b.Booking_Start_Date BETWEEN '2005-04-01' and '2005-06-30 >> > 23:59:59' >> > AND b.Booking_Type = "Booking" >> > GROUP BY m.name >> > ORDER BY m.id; >> > >> > Since I am comparing date values to date values, this should process > much >> > faster (especially if Booking_Start_Date is the leftmost column in any >> > index). The short date format works for April 1 because any date > constant >> > without a time value is considered to be midnight (00:00:00). I had to >> > include the last second of June 30 so that you would detect bookings > that >> > happened on that date (so you searched across the whole day and didn't >> > just stop at midnight at the start of the day). An alternative to > using >> > the BETWEEN...AND... comparitor for date ranges is to look for "less > than >> > the next day". >> > >> > SELECT m.name, COUNT(b.Booking_ID) AS "Num_Bookings" >> > FROM monthList m >> > LEFT JOIN Bookings b >> > ON m.id = MONTH(b.Booking_Start_Date) >> > WHERE b.User_ID = 1 >> > AND b.Work_Type_ID = 12 >> > AND b.Booking_Start_Date >= '2005-04-01' >> > AND b.Booking_Start_Date < '2005-07-01' >> > AND b.Booking_Type = "Booking" >> > GROUP BY m.name >> > ORDER BY m.id; >> > >> > And, just as an example, here is how you would handle the case where > you >> > wanted a month-by-month report that crosses over from one year to the >> > next. This should show how many bookings you had in each month for the >> > twelve months starting with April 2004. >> > >> > SELECT m.name as month, YEAR(b.Booking_Start_Date) as year, >> > COUNT(b.Booking_ID) AS "Num_Bookings" >> > FROM monthList m >> > LEFT JOIN Bookings b >> > ON m.id = MONTH(b.Booking_Start_Date) >> > WHERE b.User_ID = 1 >> > AND b.Work_Type_ID = 12 >> > AND b.Booking_Start_Date >= '2004-04-01' >> > AND b.Booking_Start_Date < '2005-04-01' >> > AND b.Booking_Type = "Booking" >> > GROUP BY 1, 2 >> > ORDER BY 1, m.id; >> > >> > (NOTE: I used a MySQL-specific shortcut by using column numbers in the >> > GROUP BY and ORDER BY clauses. A more ANSI way of writing those > clauses >> > would have been: >> > >> > GROUP BY YEAR(b.Booking_Start_Date), m.name >> > ORDER BY YEAR(b.Booking_Start_Date), m.id; >> > >> > I think the shortcut method is just a tad easier to read.) >> > >> > >> > >> > HTH! >> > >> > Shawn Green >> > Database Administrator >> > Unimin Corporation - Spruce Pine >> >> Hi Shawn, >> >> Thanks for your reply but the query doesnt seem to work for me... >> >> mysql> SELECT M.Month_Name, >> -> COUNT(B.Booking_ID) AS "Num_Bookings" >> -> FROM Months M >> -> LEFT JOIN Bookings B >> -> ON M.Month_ID = MONTH(B.Booking_Start_Date) >> -> WHERE B.User_ID = 1799 >> -> AND B.Booking_Start_Date >= '2005-01-01' >> -> AND B.Booking_Start_Date < '2005-12-01' >> -> AND B.Booking_Type = "Booking" >> -> GROUP BY M.Month_Name >> -> ORDER BY M.Month_ID; >> +------------+--------------+ >> | Month_Name | Num_Bookings | >> +------------+--------------+ >> | May | 3 | >> | June | 22 | >> | July | 12 | >> | August | 15 | >> | September | 13 | >> | October | 18 | >> | November | 6 | >> +------------+--------------+ >> 7 rows in set (0.04 sec) >> >> mysql> >> >> Any ideas why this might be happening? >> > > It seems to be working just fine. You didn't get any December bookings > because the latest possible booking occurs before '2005-12-01 00:00:00' . > If you want see December bookings, change your end date to '2006-01-01' > > If that wasn't it, can you explain WHY those numbers are wrong as I have > only the slimmest clue what your data is supposed to look like. If those > aren't the numbers you wanted, what numbers did you expect? Please, > provide sample queries to demonstrate what you expected and I can help you > figure out what we are missing. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine
Hi Shawn, I understand the fact that December isn't there but what about January - April? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
