DEV Community

Cover image for How to get several data count in a single query by applying conditions
Harut Margaryan
Harut Margaryan

Posted on

How to get several data count in a single query by applying conditions

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

  1. received grades which is above 80.
  2. received grades which is below or equal to 80.
  3. 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
Enter fullscreen mode Exit fullscreen mode

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)