SQLite IIF Query

We are using our student table. Check how to create database and student table here.

Read more on MySQL IF query here.

IIf 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
IIF(expression_status, expression_if_true, expression_if_false )

USING IIF query

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, IIF(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
Let us try a more complex query including SUM of columns. We will use our student3 table for this. Copy the code to create the table.
SELECT id,name, social + math + science  AS total,
IIF(social + math + science  > 200, 'Pass','Fail') AS Result
  FROM student3;
idnametotalResult
2Max Ruin226Pass
3Arnold170Fail
4Krish Star180Fail
5John Mike230Pass
6Alex John225Pass
7My John Rob208Pass
8Asruid255Pass
9Tes Qry208Pass
10Big John150Fail


Using aggregate function with GROUP BY

SELECT id,name, SUM(social + math + science ) as Total,
IIF(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(IIF(class='Three',1,0)) as THREE,
SUM(IIF(class='Four',1,0)) as FOUR,
SUM(IIF(class='Five',1,0)) as FIVE ,
SUM(IIF(class='Six',1,0)) as SIX,
SUM(IIF(class='Seven',1,0)) as SEVEN,
SUM(IIF(class='Eight',1,0)) as EIGHT,
SUM(IIF(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 IIF status checking.
SELECT 
COUNT(IIF(class='Three',1,NULL)) as THREE,
COUNT(IIF(class='Four',1,NULL)) as FOUR,
COUNT(IIF(class='Five',1,NULL)) as FIVE ,
COUNT(IIF(class='Six',1,NULL)) as SIX,
COUNT(IIF(class='Seven',1,NULL)) as SEVEN,
COUNT(IIF(class='Eight',1,NULL)) as EIGHT,
COUNT(IIF(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( IIF( mark <50, 1, 0 ) ) AS grade_C, 
SUM( IIF( mark BETWEEN 50 AND 69 , 1, 0 ) ) AS grade_B, 
SUM( IIF( mark >=70, 1, 0 ) ) AS grade_A 
FROM  `student` GROUP BY class
Output is here
classgrade_Cgrade_Bgrade_A
Eight001
Five003
Four054
Nine110
Seven037
Six016
Three012


Sqlite INSERT DELETE UPDATE SELECT ORDER By LIMIT
Subscribe to our YouTube Channel here

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2022 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer