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
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 .
My John Rob
Grid view with rows as columns
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
Output is here
Now let us use COUNT, here we will return NULL for False condtion of IF status checking.
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
Output is here
Let us crate a report using our student table to display total number of different grades in each 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