November 7, 2008 at 7:10 pm
I am trying to get the count of the total tests and sessions showing the description of the test and or session between a specific date but I am getting this error:
"You tried to execute a query that does not include the specified expression 'Lesson_Date' as part of an aggregate function."
SELECT Booking.Lesson_Date, Lesson_Type.Lesson_Desc, COUNT (Booking.Lesson_CatId) AS TotalTestAndSessions
FROM Booking INNER JOIN (Lesson_Type INNER JOIN Session_Information ON Lesson_Type.Lesson_TypeId = Session_Information.Lesson_TypeId) ON Booking.Booking_Id = Session_Information.Booking_Id
WHERE (((Booking.Lesson_Date) IS NOT NULL) AND (Booking.Lesson_Date BETWEEN #01-Oct-2008# AND #31-Oct-2008#));
November 7, 2008 at 8:33 pm
You need a GROUP BY, and as this isn't MS-Access, I don't think that the #..#'s are going to work:
SELECT Booking.Lesson_Date, Lesson_Type.Lesson_Desc, COUNT (Booking.Lesson_CatId) AS TotalTestAndSessions
FROM Session_Information
INNER JOIN Lesson_Type ON Lesson_Type.Lesson_TypeId = Session_Information.Lesson_TypeId
INNER JOIN Booking ON Booking.Booking_Id = Session_Information.Booking_Id
WHERE (((Booking.Lesson_Date) IS NOT NULL) AND (Booking.Lesson_Date BETWEEN '01-Oct-2008' AND '31-Oct-2008'))
GROUP BY Booking.Lesson_Date, Lesson_Type.Lesson_Desc
Also, there's no need to nest INNER JOINS, you can always just connect them serially.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply