PostgreSQL – Conditional Expressions CASE WHEN THEN

Conditional Expressions

we want to have a conditional situation where we say OK if this salary is less than a certain number then the result should be this.Otherwise make the result something else.

if they make over $100000 we could say they are paid well if they’re paid under $100000 and we could say they’re they’re underpaid.For that we use something called the case statement.

This is known as a conditional expression where you know based on this condition we want something to be returned as the output.When this condition is true then this should be returned. When this condition is true then this should be returned.

SELECT first_name, salary,
CASE
	WHEN salary > 10000 THEN 'UNDER PAID'
	WHEN salary < 10000 THEN 'PAID WELL'
END
FROM employees
ORDER BY salary desc

SELECT first_name, salary,
CASE
	WHEN salary > 10000 THEN 'UNDER PAID'
	WHEN salary < 10000 AND salary > 160000 THEN 'PAID WELL'
	ELSE 'EXECUTIVE'	
END AS category
FROM employees
ORDER BY salary desc

Excersise 1 : The assignment is for you to give me the total counts of all the executives and all the people that
are paid well and all the people that are underpaid.

SELECT a.category, count(*) FROM (
SELECT first_name, salary,
CASE
	WHEN salary > 10000 THEN 'UNDER PAID'
	WHEN salary < 10000 AND salary > 160000 THEN 'PAID WELL'
	WHEN salary < 16000 THEN 'EXECUTIVE'
	ELSE 'UNPAID'	
END AS category
FROM employees
ORDER BY salary desc
	) a 
GROUP BY a.category

You can categorize your data using case. You can also use integer here :

SELECT a.category, count(*) FROM (
SELECT first_name, salary,
CASE
	WHEN salary > 100000 THEN 0
	WHEN salary < 100000 AND salary > 160000 THEN 1
	WHEN salary < 160000 THEN 2
	ELSE 999999	
END AS category
FROM employees
ORDER BY salary desc
	) a 
GROUP BY a.category

We can transpose the data turning rows into columns. You can use the SUM function and the CASE in combining these you can transpose this data.

SELECT SUM( CASE WHEN salary > 100000 THEN 1 ELSE 0 END ) as under_paid,
SUM( CASE WHEN salary < 100000 AND salary > 150000 THEN 1 ELSE 0 END ) as paid_well,
SUM( CASE WHEN salary < 150000 THEN 1 ELSE 0 END ) as executive
FROM employees

//Output

This SUM function is going to sum all of those cases where the salary is less than 100000. The reason why it’s able to SUM this is because this is a numeric result.

Excersise 2 : Show the output in table format as shown below.

SELECT SUM ( CASE WHEN department = 'Sports' THEN 1 ELSE 0 END) as Sports,
				  SUM ( CASE WHEN department = 'Tools' THEN 1 ELSE 0 END) as Tools,
				  SUM ( CASE WHEN department = 'Clothing' THEN 1 ELSE 0 END) as Clothing,
				  SUM ( CASE WHEN department = 'Computers' THEN 1 ELSE 0 END) as Computers	   
FROM employees

Excersise 3 : Generate report like shown below

SELECT first_name, (CASE WHEN region_id = 1 THEN (SELECT country FROM regions WHERE region_id = 1) ELSE NULL END) as region_1,
(CASE WHEN region_id = 2 THEN (SELECT country FROM regions WHERE region_id = 2) ELSE NULL END) as region_2,
(CASE WHEN region_id = 3 THEN (SELECT country FROM regions WHERE region_id = 3) ELSE NULL END) as region_3,
(CASE WHEN region_id = 4 THEN (SELECT country FROM regions WHERE region_id = 4) ELSE NULL END) as region_4,
(CASE WHEN region_id = 5 THEN (SELECT country FROM regions WHERE region_id = 5) ELSE NULL END) as region_5,
(CASE WHEN region_id = 6 THEN (SELECT country FROM regions WHERE region_id = 6) ELSE NULL END) as region_6,
(CASE WHEN region_id = 7 THEN (SELECT country FROM regions WHERE region_id = 7) ELSE NULL END) as region_7
FROM employees

Excersise 4 : Generate report like shown below

SELECT count(a.region_1)+ count(a.region_2) + count(a.region_3) as United_States,
count(a.region_4)+ count(a.region_5) + count(a.region_3) as Asia,
count(a.region_6)+ count(a.region_7) as Canada
FROM (
SELECT first_name, CASE WHEN region_id = 1 THEN (SELECT country FROM regions WHERE region_id = 1) END as region_1,
CASE WHEN region_id = 2 THEN (SELECT country FROM regions WHERE region_id = 2) END as region_2,
CASE WHEN region_id = 3 THEN (SELECT country FROM regions WHERE region_id = 3) END as region_3,
CASE WHEN region_id = 4 THEN (SELECT country FROM regions WHERE region_id = 4) END as region_4,
CASE WHEN region_id = 5 THEN (SELECT country FROM regions WHERE region_id = 5) END as region_5,
CASE WHEN region_id = 6 THEN (SELECT country FROM regions WHERE region_id = 6) END as region_6,
CASE WHEN region_id = 7 THEN (SELECT country FROM regions WHERE region_id = 7) END as region_7
FROM employees
) a

Total Countries :

SELECT United_States + Asia + Canada as Total FROM (
	SELECT count(a.region_1)+ count(a.region_2) + count(a.region_3) as United_States,
	count(a.region_4)+ count(a.region_5)  as Asia,
	count(a.region_6)+ count(a.region_7) as Canada
	FROM (
		SELECT first_name, CASE WHEN region_id = 1 THEN (SELECT country FROM regions WHERE region_id = 1) END as region_1,
		CASE WHEN region_id = 2 THEN (SELECT country FROM regions WHERE region_id = 2) END as region_2,
		CASE WHEN region_id = 3 THEN (SELECT country FROM regions WHERE region_id = 3) END as region_3,
		CASE WHEN region_id = 4 THEN (SELECT country FROM regions WHERE region_id = 4) END as region_4,
		CASE WHEN region_id = 5 THEN (SELECT country FROM regions WHERE region_id = 5) END as region_5,
		CASE WHEN region_id = 6 THEN (SELECT country FROM regions WHERE region_id = 6) END as region_6,
		CASE WHEN region_id = 7 THEN (SELECT country FROM regions WHERE region_id = 7) END as region_7
		FROM employees
	) a
) b

//Output : 1000