CODEWITHSUNDEEP
sqlpostgresql
Δημητρης Αποστολακης
Δημητρης Αποστολακης

Reputation: 3

max(count(*)) error: how to find max of count function?

select room_id, count(distinct course_code) 
from "LearningActivity"  
group by room_id

enter image description here

i only need the max of the count(distinct course_code). In the photo you see in the output that we have 3 rows and i want to show only the maximum of the count which is the 3rd row with count=3. Any ideas will help because i tried to put max(count(distinct course_code) but i get the error aggregate function calls cannot be nested

Upvotes: 0

Views: 244

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521579

You could use LIMIT here:

SELECT room_id, COUNT(DISTINCT course_code) 
FROM LearningActivity
GROUP BY room_id
ORDER BY COUNT(DISTINCT course_code) DESC
LIMIT 1;

This would return the room_id and its count corresponding to the room with the highest distinct count.

For a more general solution which would capture all rooms with the highest distinct course count, should there be 2 or more which are tied, use RANK:

WITH cte AS (
    SELECT room_id, COUNT(DISTINCT course_code) AS cnt,
        RANK() OVER (ORDER BY COUNT(DISTINCT course_code) DESC) rnk
    FROM LearningActivity
    GROUP BY room_id
)

SELECT room_id, cnt
FROM cte
WHERE rnk = 1;

Upvotes: 2

Related Questions