SQL: Count Number of Rows Based on Several Conditions, Single Query

user_1316501 0
Viewed 50

I have a MySQL table that has a column called score that's a float between 0-1. I want to calculate the number of rows where the score is:

  • Greater than 0.1
  • Greater than 0.3
  • Greater than 0.6
  • Greater than 0.95

I can run a query like this:

SELECT COUNT(*)
FROM my_table
WHERE score > 0.1

And do this for each value. I'd like to do this in a single query, though. So the desired output format would be something like:

low, medium, high, very_high
200   156    123       96

Any suggestions? I think it involves a case when, something like:

SELECT COUNT(*), 
       CASE WHEN score > 0.1 THEN "low"
       CASE WHEN score > 0.3 THEN "medium"
       CASE WHEN score > 0.6 THEN "high"
       CASE WHEN score > 0.95 THEN "very_high" end as score_group
FROM my_table
GROUP BY score_group

But that doesn't account for the fact that a score of 0.4 would be both "low" and "medium", so it's not quite right.

Any thoughts would be great!

pivot case count mysql sql
2 Answers

You seem to be looking for conditional aggregation :

SELECT 
    count(*) total,
    SUM(CASE WHEN score BETWEEN 0.1 AND 0.3  THEN 1 ELSE 0 END) low,
    SUM(CASE WHEN score BETWEEN 0.3 AND 0.6  THEN 1 ELSE 0 END) medium,
    SUM(CASE WHEN score BETWEEN 0.6 AND 0.95 THEN 1 ELSE 0 END) high,
    SUM(CASE WHEN score >= 0.95 THEN 1 ELSE 0 END) 'very_high'
FROM my_table

This query will return a single line, showing the total rows analyzed and the count of rows that belong to each category. col BETWEEN val1 AND val2 is a shortcut for col >= val1 AND col < val2.

user_106767160

You're approach is quite close, you can use AND in your conditions.

This will do the thing:

SELECT COUNT(*),
CASE WHEN score > 0.1 AND score <= 0.3 THEN "low"
WHEN score > 0.3 AND score <= 0.6 THEN "medium"
WHEN score > 0.6 AND score <= 0.95 THEN "high"
WHEN score > 0.95 THEN "very_high"
ELSE "very_low"
END AS score_group
FROM my_table
GROUP BY score_group
user_57500160
Related