{"id":711,"date":"2019-09-07T13:10:17","date_gmt":"2019-09-07T07:40:17","guid":{"rendered":"http:\/\/uitutorials.in\/wp\/?p=711"},"modified":"2019-09-07T13:10:17","modified_gmt":"2019-09-07T07:40:17","slug":"postgresql-conditional-expressions-case-when-then","status":"publish","type":"post","link":"https:\/\/uitutorials.in\/wp\/postgresql-conditional-expressions-case-when-then\/","title":{"rendered":"PostgreSQL – Conditional Expressions CASE WHEN THEN"},"content":{"rendered":"

Conditional Expressions<\/h3>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

SELECT first_name, salary,\r\nCASE\r\n\tWHEN salary > 10000 THEN 'UNDER PAID'\r\n\tWHEN salary < 10000 THEN 'PAID WELL'\r\nEND\r\nFROM employees\r\nORDER BY salary desc\r\n<\/code><\/pre>\n
SELECT first_name, salary,\r\nCASE\r\n\tWHEN salary > 10000 THEN 'UNDER PAID'\r\n\tWHEN salary < 10000 AND salary > 160000 THEN 'PAID WELL'\r\n\tELSE 'EXECUTIVE'\t\r\nEND AS category\r\nFROM employees\r\nORDER BY salary desc\r\n<\/code><\/pre>\n
Excersise 1 :<\/strong> The assignment is for you to give me the total counts of all the executives and all the people that
\nare paid well and all the people that are underpaid.<\/p>\n
SELECT a.category, count(*) FROM (\r\nSELECT first_name, salary,\r\nCASE\r\n\tWHEN salary > 10000 THEN 'UNDER PAID'\r\n\tWHEN salary < 10000 AND salary > 160000 THEN 'PAID WELL'\r\n\tWHEN salary < 16000 THEN 'EXECUTIVE'\r\n\tELSE 'UNPAID'\t\r\nEND AS category\r\nFROM employees\r\nORDER BY salary desc\r\n\t) a \r\nGROUP BY a.category\r\n<\/code><\/pre>\n
You can categorize your data using case. You can also use integer here : <\/p>\n
SELECT a.category, count(*) FROM (\r\nSELECT first_name, salary,\r\nCASE\r\n\tWHEN salary > 100000 THEN 0\r\n\tWHEN salary < 100000 AND salary > 160000 THEN 1\r\n\tWHEN salary < 160000 THEN 2\r\n\tELSE 999999\t\r\nEND AS category\r\nFROM employees\r\nORDER BY salary desc\r\n\t) a \r\nGROUP BY a.category\r\n<\/code><\/pre>\n
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.<\/p>\n
SELECT SUM( CASE WHEN salary > 100000 THEN 1 ELSE 0 END ) as under_paid,\r\nSUM( CASE WHEN salary < 100000 AND salary > 150000 THEN 1 ELSE 0 END ) as paid_well,\r\nSUM( CASE WHEN salary < 150000 THEN 1 ELSE 0 END ) as executive\r\nFROM employees\r\n<\/code><\/pre>\n
\/\/Output<\/p>\n\n\n\n
under_paid<\/td>\npaid_well<\/td>\nexecutive<\/td>\n<\/tr>\n
581<\/td>\n298<\/td>\n121<\/td>\n<\/tr>\n<\/table>\n
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.<\/p>\n
Excersise 2 :<\/strong> Show the output in table format as shown below.<\/p>\n\n\n\n
Sports<\/td>\nTools<\/td>\nClothing<\/td>\nComputers<\/td>\n<\/tr>\n
34<\/td>\n39<\/td>\n49<\/td>\n47<\/td>\n<\/tr>\n<\/table>\n
SELECT SUM ( CASE WHEN department = 'Sports' THEN 1 ELSE 0 END) as Sports,\r\n\t\t\t\t  SUM ( CASE WHEN department = 'Tools' THEN 1 ELSE 0 END) as Tools,\r\n\t\t\t\t  SUM ( CASE WHEN department = 'Clothing' THEN 1 ELSE 0 END) as Clothing,\r\n\t\t\t\t  SUM ( CASE WHEN department = 'Computers' THEN 1 ELSE 0 END) as Computers\t   \r\nFROM employees\r\n<\/code><\/pre>\n
Excersise 3 :<\/strong> Generate report like shown below <\/p>\n\n\n\n\n
first_name<\/td>\nregion_1<\/td>\nregion_2<\/td>\nregion_3<\/td>\nregion_4<\/td>\nregion_5<\/td>\nregion_6<\/td>\nregion_7<\/td>\n<\/tr>\n
sydney<\/td>\nnull<\/td>\nnull<\/td>\n<\/td>\nAsia<\/td>\nnull<\/td>\nnull<\/td>\nnull<\/td>\n<\/tr>\n
Avron<\/td>\nnull<\/td>\nnull<\/td>\nnull<\/td>\nnull<\/td>\nUnited States<\/td>\nnull<\/td>\nnull<\/td>\n<\/tr>\n<\/table>\n
SELECT first_name, (CASE WHEN region_id = 1 THEN (SELECT country FROM regions WHERE region_id = 1) ELSE NULL END) as region_1,\r\n(CASE WHEN region_id = 2 THEN (SELECT country FROM regions WHERE region_id = 2) ELSE NULL END) as region_2,\r\n(CASE WHEN region_id = 3 THEN (SELECT country FROM regions WHERE region_id = 3) ELSE NULL END) as region_3,\r\n(CASE WHEN region_id = 4 THEN (SELECT country FROM regions WHERE region_id = 4) ELSE NULL END) as region_4,\r\n(CASE WHEN region_id = 5 THEN (SELECT country FROM regions WHERE region_id = 5) ELSE NULL END) as region_5,\r\n(CASE WHEN region_id = 6 THEN (SELECT country FROM regions WHERE region_id = 6) ELSE NULL END) as region_6,\r\n(CASE WHEN region_id = 7 THEN (SELECT country FROM regions WHERE region_id = 7) ELSE NULL END) as region_7\r\nFROM employees\r\n<\/code><\/pre>\n
Excersise 4 :<\/strong> Generate report like shown below <\/p>\n\n\n\n
united_states<\/td>\nasia<\/td>\ncanada<\/td>\n<\/tr>\n
438<\/td>\n264<\/td>\n298<\/td>\n<\/tr>\n<\/table>\n
SELECT count(a.region_1)+ count(a.region_2) + count(a.region_3) as United_States,\r\ncount(a.region_4)+ count(a.region_5) + count(a.region_3) as Asia,\r\ncount(a.region_6)+ count(a.region_7) as Canada\r\nFROM (\r\nSELECT first_name, CASE WHEN region_id = 1 THEN (SELECT country FROM regions WHERE region_id = 1) END as region_1,\r\nCASE WHEN region_id = 2 THEN (SELECT country FROM regions WHERE region_id = 2) END as region_2,\r\nCASE WHEN region_id = 3 THEN (SELECT country FROM regions WHERE region_id = 3) END as region_3,\r\nCASE WHEN region_id = 4 THEN (SELECT country FROM regions WHERE region_id = 4) END as region_4,\r\nCASE WHEN region_id = 5 THEN (SELECT country FROM regions WHERE region_id = 5) END as region_5,\r\nCASE WHEN region_id = 6 THEN (SELECT country FROM regions WHERE region_id = 6) END as region_6,\r\nCASE WHEN region_id = 7 THEN (SELECT country FROM regions WHERE region_id = 7) END as region_7\r\nFROM employees\r\n) a\r\n<\/code><\/pre>\n
Total Countries : <\/strong><\/p>\n
\r\nSELECT United_States + Asia + Canada as Total FROM (\r\n\tSELECT count(a.region_1)+ count(a.region_2) + count(a.region_3) as United_States,\r\n\tcount(a.region_4)+ count(a.region_5)  as Asia,\r\n\tcount(a.region_6)+ count(a.region_7) as Canada\r\n\tFROM (\r\n\t\tSELECT first_name, CASE WHEN region_id = 1 THEN (SELECT country FROM regions WHERE region_id = 1) END as region_1,\r\n\t\tCASE WHEN region_id = 2 THEN (SELECT country FROM regions WHERE region_id = 2) END as region_2,\r\n\t\tCASE WHEN region_id = 3 THEN (SELECT country FROM regions WHERE region_id = 3) END as region_3,\r\n\t\tCASE WHEN region_id = 4 THEN (SELECT country FROM regions WHERE region_id = 4) END as region_4,\r\n\t\tCASE WHEN region_id = 5 THEN (SELECT country FROM regions WHERE region_id = 5) END as region_5,\r\n\t\tCASE WHEN region_id = 6 THEN (SELECT country FROM regions WHERE region_id = 6) END as region_6,\r\n\t\tCASE WHEN region_id = 7 THEN (SELECT country FROM regions WHERE region_id = 7) END as region_7\r\n\t\tFROM employees\r\n\t) a\r\n) b\r\n<\/code><\/pre>\n
\/\/Output : 1000<\/p>\n","protected":false},"excerpt":{"rendered":"
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<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[80],"tags":[82],"class_list":["post-711","post","type-post","status-publish","format-standard","hentry","category-sql","tag-postgresql","ct-col-2"],"_links":{"self":[{"href":"https:\/\/uitutorials.in\/wp\/wp-json\/wp\/v2\/posts\/711","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/uitutorials.in\/wp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/uitutorials.in\/wp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/uitutorials.in\/wp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/uitutorials.in\/wp\/wp-json\/wp\/v2\/comments?post=711"}],"version-history":[{"count":4,"href":"https:\/\/uitutorials.in\/wp\/wp-json\/wp\/v2\/posts\/711\/revisions"}],"predecessor-version":[{"id":715,"href":"https:\/\/uitutorials.in\/wp\/wp-json\/wp\/v2\/posts\/711\/revisions\/715"}],"wp:attachment":[{"href":"https:\/\/uitutorials.in\/wp\/wp-json\/wp\/v2\/media?parent=711"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/uitutorials.in\/wp\/wp-json\/wp\/v2\/categories?post=711"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/uitutorials.in\/wp\/wp-json\/wp\/v2\/tags?post=711"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}