SQL Case When Repeating Conditions-CodePudding

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

CodePudding user response：

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;

Demo: http://sqlfiddle.com/#!17/9eecb/101807

CodePudding user response：

You want to concatenate strings. For the lack of some CONCAT_WS function skipping nulls, just have a comma with every region and trim the string accordingly in the end.

SELECT
  TRIM(',' FROM
    CASE WHEN country_code IN ('PT','IT','ES','PL','AD') THEN ',SWE' ELSE '' END ||
    CASE WHEN country_code IN ('MD','BG','BA','SI','HR','ME','RO','RS') THEN ',SEE' ELSE '' END ||
    CASE WHEN country_code IN ('CI','GH','MA','NG','UG','KE','TN') THEN ',AFRICA' ELSE '' END ||
    CASE WHEN country_code IN ('CI','GH','NG','UG','KE','TN') THEN ',SSA' ELSE '' END ||
    CASE WHEN country_code IN ('UA','BY','GE','KZ','KG','AM') THEN ',ECA' ELSE '' END
  ) AS regions,
  COUNT(DISTINCT orderid) AS amount_of_orders
FROM country
GROUP BY regions
ORDER BY regions;

UPDATE

You have complete changed your request. You answered that you want a column with a region list (e.g. 'AFRICA,SSA'), but now you show that you want one row per single region. Please don't make such fundamental changes to your requests. However, you want a union of single region queries now:

SELECT 'SWE' AS region, COUNT(DISTINCT orderid) AS amount_of_orders
FROM country
WHERE country_code IN ('PT','IT','ES','PL','AD')
UNION ALL    
SELECT 'SEE' AS region, COUNT(DISTINCT orderid) AS amount_of_orders
FROM country
WHERE country_code IN ('MD','BG','BA','SI','HR','ME','RO','RS')
UNION ALL    
SELECT 'AFRICA' AS region, COUNT(DISTINCT orderid) AS amount_of_orders
FROM country
WHERE country_code IN ('CI','GH','MA','NG','UG','KE','TN')
UNION ALL    
SELECT 'SSA' AS region, COUNT(DISTINCT orderid) AS amount_of_orders
FROM country
WHERE country_code IN ('CI','GH','NG','UG','KE','TN')
UNION ALL    
SELECT 'ECA' AS region, COUNT(DISTINCT orderid) AS amount_of_orders
FROM country
WHERE country_code IN ('UA','BY','GE','KZ','KG','AM');