I have a table called country that has 2 columns: orderid, country_code.
I need to do the following CASE WHEN:
SELECT
CASE
WHEN country_code IN ('PT','IT','ES','PL','AD') THEN 'SWE'
WHEN country_code IN ('MD','BG','BA','SI','HR','ME','RO','RS') THEN 'SEE'
WHEN country_code IN ('CI','GH','MA','NG','UG','KE','TN') THEN 'AFRICA'
WHEN country_code IN ('CI','GH','NG','UG','KE','TN') THEN 'SSA'
WHEN country_code IN ('UA','BY','GE','KZ','KG','AM') THEN 'ECA'
END AS region,
COUNT(DISTINCT orderid) AS amount_of_orders
FROM country
GROUP BY 1
However, when I run the code, Region SSA doesn't appear because the tcondition before (AFRICA) is using the countries of SSA (SSA is the same as AFRICA but without MA).
How can I achieve to have the complete amount of orders for AFRICA and SSA?
rdbms: Amazon Redshift
EDIT: This is my table right now:
SWE 200
SEE 500
AFRICA 350
SSA 0 <--- (it doesn't appear because the conditions were met by AFRICA region)
ECA 200
And I need the following:
SWE 200
SEE 500
AFRICA 350 --> (MA represent 150 orders)
SSA 200 --> (sames as AFRICA, but without MA)
ECA 200
You could define a common table expression (CTE) that contains all country codes and theirs corresponding region, then join it with your data.
WITH regions AS
(
SELECT CAST('PT,IT,ES,PL,AD' AS VARCHAR) AS country_codes, 'SWE' AS region_code
UNION ALL SELECT CAST('MD,BG,BA,SI,HR,ME,RO,RS' AS VARCHAR) AS country_codes, 'SEE' AS region_code
UNION ALL SELECT CAST('CI,GH,MA,NG,UG,KE,TN' AS VARCHAR) AS country_codes, 'AFRICA' AS region_code
UNION ALL SELECT CAST('CI,GH,NG,UG,KE,TN' AS VARCHAR) AS country_codes, 'SSA' AS region_code
UNION ALL SELECT CAST('UA,BY,GE,KZ,KG,AM' AS VARCHAR) AS country_codes, 'ECA' AS region_code
)
SELECT
regions.region_code AS region,
COUNT(DISTINCT orderid) AS amount_of_orders
FROM country
INNER JOIN regions ON STRPOS(regions.country_codes, country.country_code) > 0
GROUP BY regions.region_code;