Using AVG with HAVING

Question

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.

Remove the HAVING clause, and you will see that this does not actually calculate the average as you intended to begin with. — CBroe, Jan 23 '23 at 11:11

score 2 · Accepted Answer · answered Jan 23 '23 at 11:14

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)

Using AVG with HAVING

1 Answers1