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, 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;
You will get same output as above.
Grid view with rows as columns
We used SUM() to add all True conditions.
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
Class | grade_C | grade_B | grade_A |
Eight | 0 | 0 | 1 |
Five | 0 | 0 | 3 |
Four | 0 | 5 | 4 |
Nine | 1 | 1 | 0 |
Seven | 0 | 3 | 7 |
Six | 0 | 1 | 6 |
Three | 0 | 1 | 2 |
← SQL CASE
SQL sum in multiple columns→
← Subscribe to our YouTube Channel here