Using AVG with HAVING-CodePudding

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.

CodePudding user response：

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)