Misuse of aggregate function avg()-CodePudding

Let's say I have a table named recent_grads and it has columns major, sharewomen, where major is a string for example "engineering", "medical" etc. and sharewomen is a float for example "1230.23" etc.

I want to select and filter the rows that are greater than the average value of sharewomen column.

I want to know why this query is not working.

SELECT *
FROM recent_grads
WHERE ShareWomen > AVG(ShareWomen);

It's currently giving me this error.

(sqlite3.OperationalError) misuse of aggregate function AVG() [SQL: SELECT * FROM recent_grads WHERE ShareWomen > AVG(ShareWomen);] (Background on this error at: http://sqlalche.me/e/e3q8)

CodePudding user response：

Use a subquery to find the average over the entire table:

SELECT *
FROM recent_grads
WHERE ShareWomen > (SELECT AVG(ShareWomen) FROM recent_grads);

This is straightforward and is what I would recommend. For a more sophisticated version, you could use AVG() as an analytic function:

WITH cte AS (
    SELECT r.*, AVG(ShareWomen) OVER () AS AvgShareWomen
    FROM recent_grads r
)

SELECT *
FROM cte
WHERE ShareWomen > AvgShareWomen;