Why this query giving wrong output?

Question

I want to find review group by ratings 1,2 etc and for doing so i write below query but this giving wrong info.

This is the query:

SELECT CASE ur.rating
 WHEN ur.rating between 0.5 and 1 THEN 1
 WHEN ur.rating between 1.5 and 2 THEN 2
 WHEN ur.rating between 2.5 and 3 THEN 3
 WHEN ur.rating between 3.5 and 4 THEN 4
 WHEN (ur.rating between 4.5 and 5) THEN 5
 END As 'ratings',
 count(ur.rating)
from user_review ur
group by ratings;

That is output:

+---------+------------------+
| ratings | count(ur.rating) |
+---------+------------------+
| NULL | 10 |
| 1 | 1 |
+---------+------------------+

My accepted output will be like below:

+---------+------------------+
| ratings | count(ur.rating) |
+---------+------------------+
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 2 |
| 5 | 3 |
+---------+------------------+

Below is my user_review table with demo data:

+----+--------+---------+--------+---------------------+---------------+
| id | userId | giverId | rating | date | comments |
+----+--------+---------+--------+---------------------+---------------+
| 2 | 1 | 3 | 5 | 2019年07月21日 00:00:28 | update test 1 |
| 3 | 1 | 2 | 3 | 2019年07月21日 02:41:56 | test it |
| 4 | 1 | 3 | 3.5 | 2019年07月21日 02:42:16 | test it |
| 5 | 1 | 4 | 4 | 2019年07月21日 02:42:21 | test it |
| 6 | 1 | 5 | 4.5 | 2019年07月21日 02:42:27 | test it |
| 7 | 1 | 6 | 5 | 2019年07月21日 02:42:36 | test it |
| 8 | 1 | 7 | 1 | 2019年07月21日 02:42:41 | test it |
| 9 | 1 | 8 | 1.5 | 2019年07月21日 02:42:47 | test it |
| 10 | 1 | 9 | 2 | 2019年07月21日 02:42:57 | test it |
| 11 | 1 | 10 | 2.5 | 2019年07月21日 02:43:08 | test it |
| 12 | 1 | 12 | 0.5 | 2019年07月21日 02:49:32 | test it |
+----+--------+---------+--------+---------------------+---------------+

I also tried below query it also give same output :

SELECT count(CASE ur.rating WHEN ur.rating between .5 and 1 THEN 1 end) as '1',
 count(CASE ur.rating WHEN ur.rating between 1.5 and 2 THEN 1 end) '2',
 count(CASE ur.rating WHEN ur.rating between 2.5 and 3 THEN 1 end) '3',
 count(CASE ur.rating WHEN ur.rating between 3.5 and 4 THEN 1 end) '4',
 count(CASE ur.rating WHEN ur.rating between 4.5 and 5 THEN 1 end) '5',
 count(*)
from user_review ur;

Whats the wrong with my Query? why its not working properly?

Answer 1

Here is the real problem

The output of your query looked like this

+---------+------------------+
| ratings | count(ur.rating) |
+---------+------------------+
| NULL | 10 |
| 1 | 1 |
+---------+------------------+

When you remove the group and count functions it will look like this

+---------+
| ratings |
+---------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| 1 |
| NULL |
| NULL |
| NULL |
| NULL |
+---------+

That's because you put the comparison into the WHEN value. You do not need to use the rating variables repeatedly for each value. So the comparison for each yield boolean values. Since one of the rows has the number 1, all the other comparisons did not match up.

How to fix the Query

Don't use CASE ... WHEN. Use the IF() function

Here is your query rewritten to create the desired output

SELECT
IF
(
 ur.rating<1.5,1,
 IF(ur.rating<2.5,2,
 IF(ur.rating<3.5,3,
 IF(ur.rating<4.5,4,5)
 )
 )
) ratings,COUNT(1) FROM user_review ur
group by ratings;

Proof that this fix works

Script Load Sample Data

DROP DATABASE IF EXISTS rhidoy;
CREATE DATABASE rhidoy;
USE rhidoy
CREATE TABLE user_review
(
 id INT NOT NULL,
 userId INT,
 giverId INT,
 rating DOUBLE,
 date DATETIME,
 comments VARCHAR(30),
 PRIMARY KEY (id)
);
INSERT INTO user_review
(id,userId,giverId,rating,date,comments) VALUES
( 2,1, 3, 5 ,'2019-07-21 00:00:28','update test 1'),
( 3,1, 2, 3 ,'2019-07-21 02:41:56','test it'),
( 4,1, 3, 3.5 ,'2019-07-21 02:42:16','test it'),
( 5,1, 4, 4 ,'2019-07-21 02:42:21','test it'),
( 6,1, 5, 4.5 ,'2019-07-21 02:42:27','test it'),
( 7,1, 6, 5 ,'2019-07-21 02:42:36','test it'),
( 8,1, 7, 1 ,'2019-07-21 02:42:41','test it'),
( 9,1, 8, 1.5 ,'2019-07-21 02:42:47','test it'),
(10,1, 9, 2 ,'2019-07-21 02:42:57','test it'),
(11,1, 10, 2.5 ,'2019-07-21 02:43:08','test it'),
(12,1, 12, 0.5 ,'2019-07-21 02:49:32','test it');
SELECT * FROM user_review;

Load Sample Data and Run My Suggested Fix

mysql> DROP DATABASE IF EXISTS rhidoy;
Query OK, 1 row affected (0.33 sec)
mysql> CREATE DATABASE rhidoy;
Query OK, 1 row affected (0.08 sec)
mysql> USE rhidoy
Database changed
mysql> CREATE TABLE user_review
 -> (
 -> id INT NOT NULL,
 -> userId INT,
 -> giverId INT,
 -> rating DOUBLE,
 -> date DATETIME,
 -> comments VARCHAR(30),
 -> PRIMARY KEY (id)
 -> );
Query OK, 0 rows affected (0.34 sec)
mysql> INSERT INTO user_review
 -> (id,userId,giverId,rating,date,comments) VALUES
 -> ( 2,1, 3, 5 ,'2019-07-21 00:00:28','update test 1'),
 -> ( 3,1, 2, 3 ,'2019-07-21 02:41:56','test it'),
 -> ( 4,1, 3, 3.5 ,'2019-07-21 02:42:16','test it'),
 -> ( 5,1, 4, 4 ,'2019-07-21 02:42:21','test it'),
 -> ( 6,1, 5, 4.5 ,'2019-07-21 02:42:27','test it'),
 -> ( 7,1, 6, 5 ,'2019-07-21 02:42:36','test it'),
 -> ( 8,1, 7, 1 ,'2019-07-21 02:42:41','test it'),
 -> ( 9,1, 8, 1.5 ,'2019-07-21 02:42:47','test it'),
 -> (10,1, 9, 2 ,'2019-07-21 02:42:57','test it'),
 -> (11,1, 10, 2.5 ,'2019-07-21 02:43:08','test it'),
 -> (12,1, 12, 0.5 ,'2019-07-21 02:49:32','test it');
Query OK, 11 rows affected (0.09 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM user_review;
+----+--------+---------+--------+---------------------+---------------+
| id | userId | giverId | rating | date | comments |
+----+--------+---------+--------+---------------------+---------------+
| 2 | 1 | 3 | 5 | 2019年07月21日 00:00:28 | update test 1 |
| 3 | 1 | 2 | 3 | 2019年07月21日 02:41:56 | test it |
| 4 | 1 | 3 | 3.5 | 2019年07月21日 02:42:16 | test it |
| 5 | 1 | 4 | 4 | 2019年07月21日 02:42:21 | test it |
| 6 | 1 | 5 | 4.5 | 2019年07月21日 02:42:27 | test it |
| 7 | 1 | 6 | 5 | 2019年07月21日 02:42:36 | test it |
| 8 | 1 | 7 | 1 | 2019年07月21日 02:42:41 | test it |
| 9 | 1 | 8 | 1.5 | 2019年07月21日 02:42:47 | test it |
| 10 | 1 | 9 | 2 | 2019年07月21日 02:42:57 | test it |
| 11 | 1 | 10 | 2.5 | 2019年07月21日 02:43:08 | test it |
| 12 | 1 | 12 | 0.5 | 2019年07月21日 02:49:32 | test it |
+----+--------+---------+--------+---------------------+---------------+
11 rows in set (0.00 sec)
mysql> SELECT
 -> IF
 -> (
 -> ur.rating<1.5,1,
 -> IF(ur.rating<2.5,2,
 -> IF(ur.rating<3.5,3,
 -> IF(ur.rating<4.5,4,5)
 -> )
 -> )
 -> ) ratings,COUNT(1) FROM user_review ur
 -> group by ratings;
+---------+----------+
| ratings | COUNT(1) |
+---------+----------+
| 5 | 3 |
| 3 | 2 |
| 4 | 2 |
| 1 | 2 |
| 2 | 2 |
+---------+----------+
5 rows in set (0.00 sec)
mysql>

GIVE IT A TRY !!!

• ypercubeᵀᴹ: I will upvote when you a gix using CASE ;)
• ypercubeᵀᴹ: (.. give a fix ..)
• SQLRaptor: Rolando - Well done ! I missed that, and have updated my answer above. I have to agree with @ypercubeTM as for the CASE, the CEILING still is a better solution IMHO :-) BTW - I'm not a DBA, just a lowly SQL developer and relational designer .... As a MySQL DBA, perhaps you can help me with my question regarding roles in MySQL 8 ;-) It's still on 1st page

Answer 2

1. You don't need the long CASE, use MySQL's CEILING function - https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_ceiling
2. What is the data type for the rating column? My guess is that it's a FLOAT, and being an approximate, your actual values fall within the ranges that aren't covered by the CASE , 2.00001 for example. Change it to DECIMAL to avoid similar issues.
3. When using CASE, it's a good idea to always include an ELSE clause. Try adding ELSE -1 or other value just to troubleshoot this issue, and you will see for yourself.

HTH

UPDATE - I stand partly corrected. While my answer and points are still valid, I believe that @RickJames and @RolaondoMySQLDBA were right for this case as well.

According to IEEE 754 these number do happen to have exact representation as FLOAT, and the problem was your CASE. Using CEILING is better than CASE and IF for this, but to understand the root issue see @RolandoMySQLDBA Answer below, and @RickJames comments.

UPDATE 2019年07月21日 18:03 EDT (RolandoMySQLDBA)

In all fairness, CEILING() does make the most sense.

I ran this

SELECT CEILING(rating) ratings,COUNT(1)
FROM user_review GROUP BY CEILING(rating);

on the sample data and got this

mysql> SELECT CEILING(rating) ratings,COUNT(1)
 -> FROM user_review GROUP BY CEILING(rating);
+---------+----------+
| ratings | COUNT(1) |
+---------+----------+
| 5 | 3 |
| 3 | 2 |
| 4 | 2 |
| 1 | 2 |
| 2 | 2 |
+---------+----------+
5 rows in set (0.00 sec)

So, CEILING() is the most concise in this case. +1 to you !!!

• Rhidoy: thanks, that's working great by using ceiling() function but can you tell me why my query didn't worked? is my logic or approach was wrong or it's for only else condition? also my rating column data is float, there will be just .5 value difference, i means just, 0.5, 1, 1.5 to 5. so do i need to change it decimal?
• Rhidoy: after checking with else -1 the result is more worse, it's counting every row as its value.
• SQLRaptor: FLOAT uses an approximation of the value you provide as an INT, even though your client tool may round it up for presentation purposes. Never use FLOAT when you need to use the values in comparisons like you did, as you can't tell if it will actually be slightly smaller or slightly larger than what you expect. See dev.mysql.com/doc/refman/8.0/en/floating-point-types.html
• SQLRaptor: It's not 'worse' - it proves my point :-)
• Michael - sqlbot: @Rhidoy your original query also completely neglected any numbers with a fractional component of less than .5, such as 1.25, and your sample data had no values like that. As such, it potentially wasn't a very thorough data set so what you have here is an answer that is quite correct but premised on a question that may have been incomplete. Should 1.25 be reported as 1 or as 2? Note that CEILING(1.25) is 2.