IF Function in MySQL Query

If function will check one condition and if it is true then the it will return the Next expression ( 2nd one ) and if False then it will return the 3rd expression.

Here is the syntax
IF(expression_status, expression_if_true, expression_if_false )
In this syntax we can see how the expression is returned based on the status of first expression expression_status. If first expression is True then the 2nd expression i.e expression_if_true is returned. If expression_status is False then the 3rd expression expression_if_false is returned.

Examples of IF function

We will use our student table to display the students who have passed the exam. Mark is checked and if mark is greater than equal to 60 then the result is declared as Pass , otherwise it is Failed.

Here is the query
SELECT id,name,mark, IF(mark >=60, 'PASS','FAIL') as Result  FROM `student` LIMIT 0,10
We have limited number of results to 10 only by adding Limit query, you can go as per your requirement.

Output is here.
id name mark Result
1 John Deo 75 PASS
2 Max Ruin 85 PASS
3 Arnold 55 FAIL
4 Krish Star 60 PASS
5 John Mike 60 PASS
6 Alex John 55 FAIL
7 My John Rob 78 PASS
8 Asruid 85 PASS
9 Tes Qry 78 PASS
10 Big John 55 FAIL

Download the SQL dump of the above student table

Let us try a more complex query including SUM of columns. We will use our student3 table for this
SELECT id,name, sum(social + math + science ) as total,
if(sum(social + math + science ) > 200, 'Pass','Fail') as Result  from student3 group by id
The output is here .
id name total Result
2 Max Ruin 226 Pass
3 Arnold 170 Fail
4 Krish Star 180 Fail
5 John Mike 230 Pass
6 Alex John 225 Pass
7 My John Rob 208 Pass
8 Asruid 255 Pass
9 Tes Qry 208 Pass
10 Big John 150 Fail

Grid view with rows as columns

select
sum(if(class='Three',1,0)) as THREE,
sum(if(class='Four',1,0)) as FOUR,
sum(if(class='Five',1,0)) as FIVE ,
sum(if(class='Six',1,0)) as SIX,
sum(if(class='Seven',1,0)) as SEVEN,
sum(if(class='Eight',1,0)) as EIGHT,
sum(if(class='Nine',1,0)) as NINIE
from student 
Output is here
THREE FOUR FIVE SIX SEVEN EIGHT NINIE
3 9 3 7 10 1 2
Now let us use COUNT, here we will return NULL for False condtion of IF status checking.
select
COUNT(if(class='Three',1,NULL)) as THREE,
COUNT(if(class='Four',1,NULL)) as FOUR,
COUNT(if(class='Five',1,NULL)) as FIVE ,
COUNT(if(class='Six',1,NULL)) as SIX,
COUNT(if(class='Seven',1,NULL)) as SEVEN,
COUNT(if(class='Eight',1,NULL)) as EIGHT,
COUNT(if(class='Nine',1,NULL)) as NINIE
from student 
Output is here
THREE FOUR FIVE SIX SEVEN EIGHT NINIE
3 9 3 7 10 1 2
Let us crate a report using our student table to display total number of different grades in each class.
SELECT class, 
SUM( IF( mark <50, 1, 0 ) ) AS grade_C, 
SUM( IF( mark BETWEEN 50 AND 70 , 1, 0 ) ) AS grade_B, 
SUM( IF( mark >70, 1, 0 ) ) AS grade_A 
FROM  `student` GROUP BY class
Output is here
Classgrade_Cgrade_Bgrade_A
Eight 0 0 1
Five0 0 3
Four0 5 4
Nine1 1 0
Seven0 3 7
Six 0 1 6
Three 0 1 2
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-2017 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer