Conditional Clauses Using SELECT CASE
CASE WHEN first conditional expression THEN column value
WHEN second conditional expression THEN column value
WHEN third conditional expression THEN column value
END
CASE WHEN conditional expression THEN column value
ELSE default column value
END
sqlite> SELECT state,city|| ', ' || CASE
...> WHEN state ='AL' THEN 'Alabama'
...> WHEN state='DC' THEN 'District of Columbia'
...> WHEN state ='DE' THEN 'Delaware'
...> WHEN state='VA' THEN 'Virginia'
...> WHEN state = 'MA' THEN 'Massachusett'
...> END AS 'full name' FROM tbl_3;
state|full name
VA|Fairfax, Virginia
MA | Springfield, Massachusett
DC | Washington, District of Columbia
AL | Mobile, Alabama
sqlite>
In the following example we want to list the number of hours worked each week by the employees and to calculate any overtime that they may have worked. The CASE expression uses the condition that if the number of hours worked in a week by a given employee is greater than 40 then subtract 40 from the hours worked to calculate the amount of overtime worked, otherwise list zero in the overtime column.
sqlite> SELECT tbl_hours.week_number 'Week #', tbl_employee.employee_id 'Id Number',
tbl_employee.first_name||' ' ||tbl_employee.last_name 'Name', hours,CASE WHEN (hours>40) THEN hours-40 ELSE 0
END AS overtime FROM tbl_employee, tbl_Hours WHERE tbl_employee.employee_id = tbl_hours.employee_id ORDER BY tbl_hours.week_number,tbl_employee.employee_id;
| WEEK # | ID NUMBER | NAME | HOURS | OVERTIME |
| ---------- | ---------- | ----------------- | ---------- | ---------- |
| 1 | 50 | JONATHAN SMITH | 37.5 | 0 |
| 1 | 60 | GERALD MARSHAL | 40.25 | 0.25 |
| 2 | 50 | JONATHAN SMITH | 28.5 | 0 |
| 2 | 60 | GERALD MARSHAL | 40.25 | 0.25 |
| 3 | 50 | JONATHAN SMITH | 41.5 | 1.5 |
| 3 | 60 | GERALD MARSHAL | 41.5 | 1.5 |
| 4 | 50 | JONATHAN SMITH | 40 | 0 |
| 4 | 60 | GERALD MARSHAL | 47.75 | 7.75 |
| 5 | 50 | JONATHAN SMITH | 40 | 0 |
| 5 | 60 | GERALD MARSHAL | 40 | 0 |
The next example uses the CASE clause in an aggregate query to list the total overtime incurred by all employees for each week.
sqlite> SELECT tbl_hours.week_number 'Week #',SUM(hours) AS 'Payroll Hours' ,SUM(CASE WHEN (hours>40) THEN hours-40
...> ELSE 0
...> END) AS 'Overtime Hours' FROM tbl_employee, tbl_Hours WHERE tbl_employee.employee_id = tbl_hours.employee_id GROUP BY tbl_hours.week_number;
| WEEK # | PAYROLL HOURS | OVERTIME HOURS |
| ---------- | ------------- | ------------ |
| 1 | 77.75 | 0.25 |
| 2 | 68.75 | 0.25 |
| 3 | 83 | 3 |
| 4 | 87.75 | 7.75 |
| 5 | 80 | 0 |
Reformat Date Strings from mm/dd/yyyy to the Standard SQLite Datestring Format of yyyy-mm-dd Using SELECT CASE
Here we have a table listing dates in the conventional American format of Month, Day, Year separated by "/". Our objective is to change it to "yyyy-mm-dd" format.
sqlite> .headers on
sqlite> .mode column
sqlite> .width 10 10 16 8
sqlite> SELECT order_num,order_date,order_stat,cust_id FROM order_list;
| order_num | order_date | order_stat | cust_id |
| ---------- | ---------- | ---------------- | -------- |
| 101281 | 2/7/2008 | Completed | 650 |
| 101288 | 2/25/2008 | Completed | 453 |
| 101313 | 03/09/2008 | Cancelled | 219 |
| 101301 | 3/01/2008 | Billing Pending | 243 |
| 101316 | 06/9/2008 | In Progress | 650 |
| 101419 | 12/17/2008 | Cancelled | 219 |
Ideally the dates in the column would have two digits for the month, two for the day and 4 digits for the year, in which case it would be a simple matter of using the SUBSTR function to extract the elements and rearrange them as shown below.
SUBSTR(date_field,7,4)||'-'||SUBSTR(date_field,1,2)||'-'||SUBSTR(date_field ,4,2)
Unfortunately in the example above, some of the values in the date column have only one digit for the month and or day. Using SELECT CASE and the CAST(substring AS INTEGER) function makes it possible to extract the value of the month and the day.
(SELECT SUBSTR(date_field,-4,4)) ||'-'||(SELECT CASE WHEN CAST(SUBSTR(date_field,1,2) AS INTEGER)>=10 THEN SUBSTR(date_field,1,2) ELSE '0'|| CAST(SUBSTR (date_field,1,2) AS INTEGER) END ) ||'-'|| (SELECT CASE WHEN CAST(SUBSTR(date_field,-7,3) AS INTEGER)>=10 THEN SUBSTR(date_field,-7,2) ELSE '0'|| CAST(SUBSTR(date_field,-6,2) AS INTEGER) END) AS 'date_field'
sqlite> SELECT order_num,(SELECT SUBSTR(order_date,-4,4)) ||'-'||(SELECT CASE WHEN CAST(SUBSTR(order_date,1,2) AS INTEGER)>=10 THEN SUBSTR(order_date,1,2) ELSE '0'|| CAST(SUBSTR (order_date,1,2) AS INTEGER) END ) ||'-'|| (SELECT CASE WHEN CAST(SUBSTR(order_date,-7,3) AS INTEGER)>=10 THEN SUBSTR(order_date,-7,2) ELSE '0'|| CAST(SUBSTR(order_date,-6,2) AS INTEGER) END) AS 'order_date', order_stat,cust_id FROM order_list;
| order_num | order_date | order_stat | cust_id |
| ---------- | ---------- | ---------------- | -------- |
| 101281 | 2008-02-07 | Completed | 650 |
| 101288 | 2008-02-25 | Completed | 453 |
| 101313 | 2008-03-09 | Cancelled | 219 |
| 101301 | 2008-03-01 | Billing Pending | 243 |
| 101316 | 2008-06-09 | In Progress | 650 |
| 101419 | 2008-12-17 | Cancelled | 219 |
UPDATE table_name SET date_field = (select substr(date_field,-4,4)) ||'-'|| (SELECT CASE WHEN CAST(SUBSTR(date_field,1,2) AS INTEGER) >= 10 THEN SUBSTR(date_field,1,2) ELSE '0'|| CAST(SUBSTR (date_field,1,2) AS INTEGER) END ) ||'-'|| (SELECT CASE WHEN CAST(SUBSTR(date_field,-7,3) AS INTEGER)>=10 THEN SUBSTR(date_field,-7,2) ELSE '0'|| CAST(SUBSTR(date_field,-6,2) AS INTEGER) END);
NOTE: When doing a mass update in a table it is often a good idea to make a back up of the table before doing your changes.
sqlite> UPDATE order_list SET order_date = (SELECT SUBSTR(order_date,-4,4)) ||'-'||(SELECT CASE WHEN
CAST(SUBSTR(order_date,1,2) AS INTEGER)>=10 THEN SUBSTR(order_date,1,2) ELSE '0'||CAST(SUBSTR (order_date,1,2) AS INTEGER) END )||'-'||(SELECT CASE WHEN CAST(SUBSTR(order_date,-7,3)
AS INTEGER)>=10 THEN SUBSTR(order_date,-7,2) ELSE '0'|| CAST(SUBSTR(order_date,-6,2) AS INTEGER) END);
sqlite> SELECT order_num,order_date,order_stat,cust_id FROM order_list;;
| order_num | order_date | order_stat | cust_id |
| ---------- | ---------- | ---------------- | -------- |
| 101281 | 2008-02-07 | Completed | 650 |
| 101288 | 2008-02-25 | Completed | 453 |
| 101313 | 2008-03-09 | Cancelled | 219 |
| 101301 | 2008-03-01 | Billing Pending | 243 |
| 101316 | 2008-06-09 | In Progress | 650 |
| 101419 | 2008-12-17 | Cancelled | 219 |
Cross Tab Query
It is sometimes useful to organize data by column in a cross tab query in order to compare subsets of data. In following example we have a table with 12 rows called "Sales2008" listing monthly sales and expenses. The record for each month is identified by an integer field called "period" with a value between 1 (January) and 12 (December). The objective here is to compare sales by quarter. This can be done by creating a CASE statement for each column which represents a three month period or quarter.
sqlite>.HEADERS ON
sqlite> .MODE COLUMN
sqlite> SELECT SUM (CASE WHEN period BETWEEN 1 AND 3 THEN sales_amount ELSE 0 END) AS '1st Qtr',
...> SUM (CASE WHEN period BETWEEN 4 AND 6 THEN sales_amount ELSE 0 END) AS '2nd Qtr',
...> SUM (CASE WHEN period BETWEEN 7 AND 9 THEN sales_amount ELSE 0 END) AS '3rd Qtr',
...> SUM (CASE WHEN period BETWEEN 10 AND 12 THEN sales_amount ELSE 0 END) AS '4th Qtr',
...>SUM(sales_amount) AS 'Totals for 2008' FROM Sales2008;
| 1st Qtr | 2nd Qtr | 3rd Qtr | 4th Qtr | Totals for 2008 |
| ---------- | ---------- | ---------- | ---------- | --------------- |
| 42410.46 | 36290.93 | 29841.67 | 25719.7 | 134262.76 |
Note that the result set is one row and that the chosen format is ".MODE COLUMN" with ".HEADERS ON"
The next example utilizes the structure of the above query joined by UNION ALL clauses to show quarterly sales, expenses and net profit. A row header has been added to the beginning of each SELECT statement
SELECT 'Gross Sales' AS ' ' ,
SUM (CASE WHEN period BETWEEN 1 AND 3 THEN sales_amount ELSE 0 END) AS '1st Qtr',
SUM (CASE WHEN period BETWEEN 4 AND 6 THEN sales_amount ELSE 0 END) AS '2nd Qtr',
SUM (CASE WHEN period BETWEEN 7 AND 9 THEN sales_amount ELSE 0 END) AS '3rd Qtr',
SUM (CASE WHEN period BETWEEN 10 AND 12 THEN sales_amount ELSE 0 END) AS '4th Qtr',
SUM(sales_amount) AS 'Totals for 2008' FROM Sales2008
UNION ALL
SELECT 'Expenses' AS ' ',
SUM (CASE WHEN period BETWEEN 1 AND 3 THEN expenses ELSE 0 END) AS '1st Qtr',
SUM (CASE WHEN period BETWEEN 4 AND 6 THEN expenses ELSE 0 END) AS '2nd Qtr',
SUM (CASE WHEN period BETWEEN 7 AND 9 THEN expenses ELSE 0 END) AS '3rd Qtr',
SUM (CASE WHEN period BETWEEN 10 AND 12 THEN expenses ELSE 0 END) AS '4th Qtr',
SUM (expenses ) AS 'Totals for 2008' FROM Sales2008
UNION ALL
SELECT 'Net Profit' AS ' ',
SUM (CASE WHEN period BETWEEN 1 AND 3 THEN (sales_amount - expenses) ELSE 0 END) AS '1st Qtr',
SUM (CASE WHEN period BETWEEN 4 AND 6 THEN (sales_amount - expenses) ELSE 0 END) AS '2nd Qtr',
SUM (CASE WHEN period BETWEEN 7 AND 9 THEN (sales_amount - expenses) ELSE 0 END) AS '3rd Qtr',
SUM (CASE WHEN period BETWEEN 10 AND 12 THEN (sales_amount - expenses) ELSE 0 END)
AS '4th Qtr',
SUM(sales_amount- expenses) AS 'Totals for 2008' FROM Sales2008;
| 1st Qtr | 2nd Qtr | 3rd Qtr | 4th Qtr | Totals for 2008 | |
| ----------- | ---------- | ---------- | ---------- | ---------- | --------------- |
| Gross Sales | 42410.46 | 36290.93 | 29841.67 | 25719.7 | 134262.76 |
| Expenses | 36105.89 | 35809.12 | 33382.55 | 28798.77 | 134096.33 |
| Net Profit | 6304.57 | 481.81 | -3540.88 | -3079.07 | 166.42999999999 |
Colin Riley -- February 2009 --