Is there anyway to include an If/Then statement in my query?-CodePudding

Write a query to break down the groups by ratings, showing the count of groups with no ratings, as well as a count of each of the following ranges: 1-1.99, 2-2.99, 3-3.99, 4-4.99, and 5.

Note: If a group has no ratings, its rating will appear as "0" in the ratings column of the grp table.

Use a CASE WHEN or IF/THEN statement to categorize the ratings.

So far I have

SELECT COUNT(CASE WHEN rating between '1-1.99') FROM grp;

But I get error code 1064 SQL syntax, any help with be much appreciated. ERD TABLE

CodePudding user response：

I think I understand your question although you have not mentioned any sample data and required sample output: What you should do is use CASE-WHEN statements to put your values into specific ranges and group by these ranges.

Something like:

SELECT 
(CASE WHEN rating BETWEEN 1 AND 1.99 THEN '1-1.99'
WHEN rating BETWEEN 2 AND 2.99 THEN '2-2.99'
WHEN rating BETWEEN 3 AND 3.99 THEN '3-3.99'
WHEN rating BETWEEN 4 AND 4.99 THEN '4-4.99'
WHEN rating =5 THEN '5'
ELSE '0' END) AS ranges,
COUNT(*) AS rangeCount
 FROM grp
 GROUP BY ranges

CodePudding user response：

I think you have to use common table expression (CTE). Something like:

with cte as 
    (SELECT 
    rating,
    (CASE WHEN rating BETWEEN 1 AND 1.99 THEN '1-1.99'
    WHEN rating BETWEEN 2 AND 2.99 THEN '2-2.99'
    WHEN rating BETWEEN 3 AND 3.99 THEN '3-3.99'
    WHEN rating BETWEEN 4 AND 4.99 THEN '4-4.99'
    WHEN rating =5 THEN '5'
    ELSE '0' END) AS ranges
    FROM grp)
select 
    ranges,
    count(ranges)
from cte
group by ranges;

The output should be like: sample