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
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.
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 condition 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 69 , 1, 0 ) ) AS grade_B,
SUM( IF( mark >=70, 1, 0 ) ) AS grade_A
FROM `student` GROUP BY class