CODEWITHSUNDEEP
sqlteradata
NBC
NBC

Reputation: 1698

Using CASE WHEN

I have the following table:

Email | value | group
 a    |   1   |   x
 a    |   2   |   y
 a    |   3   |   y
 b    |   3   |   z

I previously wrote code to pull the sum of values by distinct email in each group, to get this:

Email | value | group
 a    |   1   |   x
 a    |   5   |   y
 b    |   3   |   z

The code looked like this:

 SELECT distinct email,
        group,
        sum (value)
  from t
 group by email, group

Now, I want to sum by groups of groups using CASE, to get this output:

 Email | value | super_group
 a     |   6    |   x_y
 b     |   3    |   z

But this code doesn't work:

 SELECT distinct email,
        CASE when group in (x,y) 
             then 'x_y'
             else 'z' 
        end as super_group,
        sum (value)
   from t
  group by email, super_group

Upvotes: 1

Views: 184

Answers (2)

JNevill
JNevill

Reputation: 50034

If you are using a newer version of Teradata you can use XMLAGG() to do this:

SELECT 
    email,
    sum(value),
    trim(trailing '_' FROM (XMLAGG(group || '_' ORDER BY group) (VARCHAR(50))))
FROM table
GROUP BY 1

Upvotes: 1

Jorge Campos
Jorge Campos

Reputation: 23361

You can use ARRAY_AGG

Also, if you are using a GROUP BY you don't need the DISTINCT. Try this way:

First you will have to define an ARRAY Type like this:

-- note the varchar with size 20 is a sample you should pick yours
-- and the size of the array is also an example
CREATE TYPE group_array AS VARCHAR(20) ARRAY[100];

Then you can make your query return the array type with the group value aggregated.

SELECT email,
       sum (value) as value,
       ARRAY_AGG(group, NEW group_array()) as super_group
 FROM t
GROUP BY email

That should give you as result:

 email   | value  | super_group
   a     |   6    |   ( x, y )
   b     |   3    |   ( z )

Reference to ARRAY_AGG

Upvotes: 1

Related Questions