"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
