locked
avg with innerjoin RRS feed

  • Question

  • User-1181481928 posted

    HI Everyone I have i'm trying to get avg of sick leave hours of employee and show employee details

    USE AdventureWorks2014;
    GO 

    SELECT
    PR.FirstName ,PR.LastName ,E.BirthDate, E.Gender ,E.SickLeaveHours
    FROM HumanResources.Employee E

    INNER JOIN Person.Person PR
    ON E.BusinessEntityID = PR.BusinessEntityID
    WHERE E.SickLeaveHours= AVG(E.SickLeaveHours)
    GROUP BY E.BusinessEntityID ;
    GO

     error message is 

    Msg 147, Level 15, State 1, Line 10
    An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

    Saturday, September 15, 2018 10:41 AM

All replies

  • User1724605321 posted

    Hi Mohannad,

    To get the avg of sick leave hours of the members , you can try :

     SELECT AVG(SickLeaveHours)
    FROM
    (
       SELECT SUM( SickLeaveHours ) AS SickLeaveHours
    FROM HumanResources.Employee GROUP BY BusinessEntityID 
    ) as inner_query

    Then you could group by each member by BusinessEntityID  and get the total SickLeaveHours  , then append query .

    Best Regards,

    Nan Yu

    Monday, September 17, 2018 6:48 AM