CODEWITHSUNDEEP
mysqlsql
marshN
marshN

Reputation: 31

why in this sql, function AVG() must be in subquery?

SELECT NAME，SALARY
FROM STAFF
WHERE SALARY>(SELECT AVG(SALARY) FROM SFAFF)
AND YEARS < (SELECT AVG(YEARS) FROM STAFF)

why in this sql, function AVG must be in subquery?
why the sql below is wrong?

SELECT NAME，SALARY
FROM STAFF
WHERE SALARY>AVG(SALARY)
AND YEARS < AVG(YEARS)

Upvotes: 0

Views: 82

Answers (2)

Tom J Muthirenthi
Tom J Muthirenthi

Reputation: 3340

AVG and other aggregate functions work on sets of data. The WHERE clause does not have access to the entire set, only to data for the row it is operating on. You may go for having clause after the group by, while dealing with data sets.

Upvotes: 3

fosjo
fosjo

Reputation: 27

I think you're missing the GROUP BY clause. Every aggregate functions needs a GROUP BY in order to function properly. 

SELECT  NAME，SALARY
FROM    STAFF
WHERE   SALARY > (
                    SELECT  AVG(SALARY)
                    FROM    SFAFF
                    GROUP BY [some_unique_value]
                    )
        AND YEARS < (
                        SELECT  AVG(YEARS)
                        FROM    STAFF
                        GROUP BY [some_unique_value]
                    )

Upvotes: 0

Related Questions