Here I am gonna explain how it is possible to obtain multiple counts in one SQL query for speeding up purposes
Let's assume we have students table which looks like this
students |
---|
id |
name |
grade |
course_id |
Grade field can have an integer between 40 - 100 or null if there is no grade yet.
We wanna get count of students who
- received grades which is above 80.
- received grades which is below or equal to 80.
- that haven't received any.
In generic situations we may create and do 3 requests, but this approach is costly as any separate attempt to query from database adss up some timing.
So the look of the query looks like this
SELECT
COUNT(CASE WHEN grade > 80 THEN students.id END) as group_1,
COUNT(CASE WHEN grade <= 80 THEN students.id END) as group_2,
COUNT(CASE WHEN grade is null then students.id END) as group_3
FROM students
Rather than generating separate queries, the conditions are inserted inside counts and everything is done inside SQL code.
P.S.
I used Postgresql
database for testing the query
Top comments (0)