CASE condition & value matching in SELECT Query

There are two different types of CASE quires we will learn here.

First Type
MySQL Query Comparing with value, Syntax is here
CASE A_value
    WHEN B_value THEN B_statement
    [WHEN C_Value THEN C_statement] ...
    [ELSE X_statement]
END CASE
Here A_value is matched with different values given under WHEN. If A_value equals to B_value then B_statement is executed. If it is not matched than next WHEN value C_value is checked. If nothing is matched then statement under X_statement is executed.

Let us try one example
We will use our student table for this example. We have allotted rooms for the classes at different floors in a school. Our list should display student details with class and location of the class. Using CASE in our query statement is here.
SELECT  `id` ,  `name` ,  `class` ,  `mark` ,  `sex` , 
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`
id name class mark sex location
1 John Deo Four 75 male 1st floor
2 Max Ruin Three 85 male 2nd floor
3Arnold Three 55 male 2nd floor
4 Krish Star Four 60 male 1st floor
5 John Mike Four 60 male 1st floor
6 Alex John Four 55 male 1st floor
7 My John Rob Fifth 78 male Ground floor
8 Asruid Five 85 male 2nd floor
9 Tes Qry Six 78 male Ground floor
10 Big John Four 55 male 1st floor
There are more records , here it is limited to 10 only.

We can display based on the floor wise or location wise by using order by query
SELECT  `id` ,  `name` ,  `class` ,  `mark` ,  `sex` , 
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
Second Type (Matching condition with CASE statement)
In this case we don't specify any value or data to be matched, instead we work on testing some conditions and if it is True then respective statement is to be executed.
CASE
    WHEN condition_to_check THEN statement
    [WHEN condition_to_check THEN statement ...
    [ELSE statement]
END CASE
Here we check the condition and if it is True then the respective statement is executed.
Example :
We will try our student example. Here student grade is awarded based on the mark they got. Here is the query
SELECT  `id` ,  `name` ,  `class` ,  `mark` ,  `sex` , 
CASE 
WHEN mark >= 90 THEN   'A'
WHEN mark >= 80 THEN    'B'
WHEN mark >= 70 THEN   'C'
ELSE 'FAIL'
END AS  grade
FROM  `student`
id name class mark sex grade
1 John Deo Four 75 male C
2 Max Ruin Three 85 male B
3 Arnold Three 55 male FAIL
4 Krish Star Four 60 male FAIL
5 John Mike Four 60 male FAIL
6 Alex John Four 55 male FAIL
7 My John Rob Fifth 78 male C
8 Asruid Five 85 male B
9 Tes Qry Six 78 male C
10 Big John Four 55 male FAIL
11 Ronald Six 89 male B
12 Recky Six 94 male A
13 Kty Seven 88 male B

Using BETWEEN Query

While grouping in a rang it is better to use MySQL BETWEEN Query

SELECT  `id` ,  `name` ,  `class` ,  `mark` ,  `sex` , 
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`

Download the SQL dump of the above student table

Example

By using SQL CASE we can get records based on Financial Year wise.

Visitors Rating
Your Rating




Google+

Post Comment This is for short comments only. Use the forum for more discussions.








HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2018 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer