Mysql – how to do this sql

I have a “reviews” table with N records with 3 fields.

Field ID, field name and score field.

What I am trying to do is a query that returns me a grouping by the “score” field but with values ​​between sections.

In other words, I want to group the records with a score from 0 to 1 … records with a score from 1 to 2 … and so on up to 5.

Now , i’m doing it with 5 different sql like this

    Select ifnull(avg(score),0) as avg_score,count(*) as count from reviews where score between 0 and 1 and status=1;

    Select ifnull(avg(score),0) as avg_score,count(*) as count from reviews where score between 1.01 and 2 and status=1;

Answer

If I understand correctly, you are looking for something like below. If you need you can add other columns as well in GROUP BY.

SELECT COUNT(*),
CASE
    WHEN score <= 1 THEN '0-1'
    WHEN score <= 2 THEN '1-2'
    WHEN score <= 3 THEN '2-3'
    WHEN score <= 4 THEN '3-4'
    WHEN score <= 5 THEN '4-5'
    ELSE 'Other'
END 
FROM reviews
GROUP BY
CASE
    WHEN score <= 1 THEN '0-1'
    WHEN score <= 2 THEN '1-2'
    WHEN score <= 3 THEN '2-3'
    WHEN score <= 4 THEN '3-4'
    WHEN score <= 5 THEN '4-5'
    ELSE 'Other'
END