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.
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.
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
Let us try a more complex query including SUM of columns. We will use our student3 table for this.
SELECT id,name, social + math + science AS total,
IF(social + math + science > 200, 'Pass','Fail') AS Result
FROM student3;
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
Using aggregate function with GROUP BY
SELECT id,name, SUM(social + math + science ) as Total,
if(SUM(social + math + science ) > 200, 'Pass','Fail') as Result
from student3 GROUP BY id;
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