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

Question

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!

Reverse the order of you CASE WHEN statements.

PM 77-1
– PM 77-1

2019-01-16 23:17:58 +00:00
Commented Jan 16, 2019 at 23:17 — PM 77-1
– PM 77-1, Commented Jan 16, 2019 at 23:17

GMB · Accepted Answer · 2019-01-16 23:27:31Z

3

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.

edited Jan 16, 2019 at 23:27

answered Jan 16, 2019 at 23:21

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SomoKRoceS · Accepted Answer · 2019-01-16 23:27:28Z

1

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

answered Jan 16, 2019 at 23:27

SomoKRoceS

3,0733 gold badges24 silver badges33 bronze badges

1 Comment

Gordon Linoff Over a year ago

This is better than using between, because no values will be counted multiple times.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related