Aggregate mostly same rows with few different column values

Analytics
Enthusiast

Aggregate mostly same rows with few different column values

Base data set 

Cust_idValue 1Value2
a1UK
a1US
a1FR
b2UK
c3FR

 

expected data set

Cust_idValue 1Value2
a1Both
a1Both
a1Both
b2UK Only
c3Abroad Only

 

How to do that?

 

Thanks

3 REPLIES
Enthusiast

Re: Aggregate mostly same rows with few different column values

sorry expected data set is 

 

Cust_idValue 1Value2
a1Both
b2UK Only
c3Abroad Only

Thanks

Teradata Employee

Re: Aggregate mostly same rows with few different column values

please check below SQL

SyntaxEditor Code Snippet


SEL
CUST_ID , VAL1, CASE WHEN VAL1=1 THEN 'BOTH' WHEN VAL1=2 THEN VAL2||' ' ||'ONLY' ELSE 'ABROAD '||' ' ||'ONLY' END AS VAL FROM CUST order by 2 qualify row_number() over ( partition by cust_id , val1 order by val1)=1
Junior Contributor

Re: Aggregate mostly same rows with few different column values

You should apply conditional aggregation (assuming the Value2 is NOT NULL):

SELECT 
   Cust_id,
   Value1,
   CASE -- no UK, only other countries
      WHEN Min(CASE WHEN Value2 = 'UK' THEN Value2 end) IS NULL
        THEN 'Abroad only'
        -- there is UK, but no other country 
      WHEN Min(CASE WHEN Value2 <> 'UK' THEN Value2 end) IS NULL
        THEN 'UK only' 
      ELSE 'BOTH'
   END
FROM tab
GROUP BY
   Cust_id,
   Value1