<[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > "Shaun" <[EMAIL PROTECTED]> wrote on 08/31/2005 04:56:23 PM: > >> >> <[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? >> >> > > D'OH! ROFL! > > It's completely MY fault!! The date and other restrictions based on the > Bookings table should have been part of the ON sub-clause of the LEFT JOIN > (the word OUTER is optional with MySQL. LEFT JOIN is equivalent to LEFT > OUTER JOIN). > > By leaving them in the WHERE clause, I made the dates a required part of > the result when they should have applied to the optional portion of the > data on the "right" side of the LEFT JOIN. > > The corrected query should read: > 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) > AND 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; > > I am so very sorry! Modify my other examples in the same way and they > should work too. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine
Thanks Shawn :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
