How to Compare COUNT of Groups in Percentage in SQL?

I really new to SQL, currently learning PostgreSQL.

Suppose I have the following schema:

class(class_id, school_name), primary key: (class_id)

enroll(student_id, class_id), primary key: (student_id, class_id)

I want to find the class_id where the class’ sum of enrolled student is at least 10% higher than average.

I know that I can find sum of enrollment for any class by

SELECT E.class_id, COUNT(*)
FROM enroll E
GROUP BY E.class_id

But how do I compare one to the other in percentage?

Answer

here’s your query, you can use avg() and having clause

select t1.class_id, t1.ct 
from
    (select count(1)ct, class_id
    from enroll 
    group by class_id) t1
group by t1.class_id
having avg(t1.ct) > (t1.ct * .10)

Advertisement

Answer