MySQL case – when –then to match against value or condition with GROUP by and BETWEEN query
https://www.plus2net.com/sql_tutorial...
We can compare values or condition with column data by using CASE – WHEN queries. For our sample student table we will use this query to assign Grade to our students.
CASE A_value
WHEN B_value THEN B_statement
[WHEN C_Value THEN C_statement] ...
[ELSE X_statement]
END CASE
SELECT `id` , `name` , `class` , `mark` , `gender` ,
CASE class
WHEN 'four' THEN '1st floor'
WHEN 'five' THEN '2nd floor'
WHEN 'three' THEN '2nd floor'
WHEN 'two' THEN '1st floor'
ELSE 'Ground floor'
END AS location
FROM `student` ORDER BY location
We have included ORDER BY in above query to display the rows in the order of the location column we created.
We can use condition comparison with CASE query.
SELECT `id` , `name` , `class` , `mark` , `gender` ,
CASE
WHEN mark greater than or equal to 90 THEN 'A'
WHEN mark greater than or equal to 80 THEN 'B'
WHEN mark greater than or equal to 70 THEN 'C'
ELSE 'FAIL'
END AS grade
FROM `student`
We can also use BETWEEN … AND with CASE command to assign grade.
SELECT `id` , `name` , `class` , `mark` , `gender` ,
CASE
WHEN mark BETWEEN 90 AND 100 THEN 'A'
WHEN mark BETWEEN 80 AND 89 THEN 'B'
WHEN mark BETWEEN 70 AND 79 THEN 'C'
ELSE 'FAIL'
END AS grade
FROM `student`
GROUP BY we can use along with case
SELECT class, count(*) as Total,
sum(CASE WHEN gender ='male' THEN 1 ELSE 0 END) as Male,
sum(CASE WHEN gender ='Female' THEN 1 ELSE 0 END) as Female
FROM student group by class;
#case #sql_case #case_between #case_group_by
