Search code examples
mysqlsqlaveragehaving

Using AVG with HAVING

I looked at a similar question and read the documentation on the average function, but when I tried:

CREATE TABLE tab(
id      INT,
score   INT
);

INSERT INTO tab VALUES
(1, 22),
(2, 45),
(3, 82),
(4, 87);

SELECT score,AVG(score)
FROM tab 
GROUP BY score 
HAVING score>AVG(score)

which puts AVG after score, I get

There are no results to be displayed.

How can I get this to work?

Here's the fiddle.

Solution

  • The problem here is that you're aggregating per "score". Doing such operation will bring you the average among one value for each record, hence being AVG(score) = score always.

    You need to use the corresponding window function for that purpose (Demo):

    WITH cte AS (
    SELECT score, AVG(score) OVER() AS average_score
    FROM tab 
)
SELECT * 
FROM cte
WHERE score > average_score

    or if you don't mind not selecting the average in your final output (Demo):

    SELECT score
FROM tab
WHERE score > (SELECT AVG(score) FROM tab)