Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009
    Posts
    50

    Unanswered: Not part on an aggregate function

    Sorry for the nube question. I have two tables. One for employee profiles and one for employee performance stats. Each employee's performance is rated on the group they are in. In this case thier productivity type or EmpProdType in the profile table.

    Im writing a query for a report that shows the date, employee name, calls in, calls out, tickets opened, Tickets closed, and the productivity from the performance table.

    In the report, I need to group the employees by EmpProdType(group) and sort by productivity, decending.

    Here is query that I'm trying to use.

    SELECT Emp_Stats_Daily.Perf_Daily_Date, Emp_Stats_Daily.Perf_Daily_Name, Emp_Stats_Daily.Perf_Daily_CallIn, Emp_Stats_Daily.Perf_Daily_CallOut, Emp_Stats_Daily.Perf_Daily_TicksOpen, Emp_Stats_Daily.Perf_Daily_TicksClose, Emp_Stats_Daily.Perf_Daily_Prod, Emp_Profile.EmpProdType

    FROM Emp_Stats_Daily INNER JOIN Emp_Profile ON Emp_Stats_Daily.EmpID = Emp_Profile.EmpID

    WHERE (((Emp_Stats_Daily.[Perf_Daily_Date])=(Select ParamValue from ParamTable where ParamName = "SelectedDate")))

    GROUP BY Emp_Profile.EmpProdType
    ORDER BY Emp_Stats_Daily.[Perf_Daily_Prod] DESC;

    When I run this I get an error saying:
    "You tried to execute a query that does not include 'Perf_Daily_Date' as part of an aggrogate function"
    When I add it to the GROUP BY, it says the same thing for Perf_Daily_Name.

    What am I doing wrong??

    P.S. The ParamTable is an unfortunate result of using C# Express and MS Access for reporting.

    Thanks for you time
    Jim

  2. #2
    Join Date
    Dec 2009
    Posts
    50
    I got it figured out.

    SELECT Emp_Stats_Daily.Perf_Daily_Date, Emp_Stats_Daily.Perf_Daily_Name, Emp_Stats_Daily.Perf_Daily_CallIn, Emp_Stats_Daily.Perf_Daily_CallOut, Emp_Stats_Daily.Perf_Daily_TicksOpen, Emp_Stats_Daily.Perf_Daily_TicksClose, Emp_Stats_Daily.Perf_Daily_Prod, Emp_Profile.EmpProdType

    FROM Emp_Stats_Daily INNER JOIN Emp_Profile ON Emp_Stats_Daily.EmpID = Emp_Profile.EmpID

    WHERE (((Emp_Stats_Daily.[Perf_Daily_Date])=(Select ParamValue from ParamTable where ParamName = "SelectedDate")))

    GROUP BY Emp_Profile.EmpProdType <-- remove this line
    ORDER BY Emp_Stats_Daily.[Perf_Daily_Prod] DESC;

    Changed to

    ORDER BY Emp_Profile.EmpProdType, Emp_Stats_Daily.[Perf_Daily_Prod] DESC;

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jbedson View Post
    When I run this I get an error saying:
    "You tried to execute a query that does not include 'Perf_Daily_Date' as part of an aggrogate function"
    When I add it to the GROUP BY, it says the same thing for Perf_Daily_Name.
    you're going to keep getting that error message for any un-aggregated column in the SELECT clause that isn't included in the GROUP BY clause

    an aggregated column would be something like AVG(foo) or SUM(bar), so foo and bar would thus not need to be in the GROUP BY clause

    of course, if you didn't really intend to GROUP BY after all, but simply ORDER BY, then the message goes away
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •