Using COUNT(*) aggregate function to restrict/limit output-CodePudding

I have a query that works fine on PostgreSQl v12.3.

SELECT player01, player02, COUNT(*) AS score_count
FROM results
GROUP BY player02, player01
ORDER BY score_count DESC, player01 ASC
LIMIT 10
;

What I want to achieve is to return the score_count of the player combinations that is greater than 4 (or greater as the season progresses)

Using a WHERE statment at the end of the query I get the following error.

SELECT player01, player02, COUNT(*) AS score_count
FROM results
GROUP BY player02, player01
ORDER BY score_count DESC, player01 ASC
WHERE score_count > 4
;

ERROR:  syntax error at or near "WHERE"
LINE 5: WHERE score_count > 4

Placing a WHERE statement within the query I get the following error.

SELECT player01, player02, COUNT(*) AS score_count
FROM results
WHERE score_count > 4
GROUP BY player02, player01
ORDER BY score_count DESC, player01 ASC
;

ERROR:  column "score_count" does not exist
LINE 3: WHERE score_count > 4

And also

SELECT star01, star02, COUNT(*) AS num_count
FROM results
WHERE count(*) > 4
GROUP BY star02, star01
ORDER BY num_count DESC, star01 ASC
;

ERROR:  aggregate functions are not allowed in WHERE
LINE 3: WHERE count(*) > 4

I have tried other combinations that return one or other of these errors. HAVING score_count > 4 produces the same errors.

Or is there a better/alternative way to do this?

CodePudding user response：

HAVING score_count > 4 produces the same errors.

No, it doesn't. You just have to move the clause after GROUP BY:

SELECT star01, star02, count(*) AS num_count
FROM   results
GROUP  BY star01, star02
HAVING count(*) > 4                          -- here !
ORDER  BY num_count DESC, star01;