Post reply

Not part of an aggregate function

  • November 7, 2008 at 7:10 pm

    #72980

    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

    #895905

    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