Redshift SQL sumup amount from two groups only when group 1 is found

I have a requirement where I have to add amounts from two groups only if first group exists.

So far, i have selected distinct groups and the corresponding ids. But i am not sure how to proceed further.

sample selected source Data :

Id   type  field1    field2    amount
1234 type1 testdata1 testdata2 123.5
1234 type2 testdata1 testdata2 123.5
1234 type3 testdata3 testdata4 123.5
5123 type2 testdata1 testdata2 147.96
3425 type3 testdata1 testdata2 123.5
5678 type1 testdata1 testdata2 123.5
5678 type4 testdata1 testdata2 123.5
5678 type6 testdata1 testdata2 123.5
5678 type7 testdata1 testdata2 123.5
8790 type1 testdata1 testdata2 123.5

Now i need to generate a table like below:

id, field1, field2, sum(amount) if (type = type1 and amount <> 0) or (type = type2 and amount <> 0), this should be done only when type 1 is present else no record created.

i.e. sample data to be generated

Id   field1    field2    sum(amount)
1234 testdata1 testdata2 247.00
5678 testdata1 testdata2 123.5
8790 testdata1 testdata2 123.5

The code i have written (provided below) generates sum(amount) or amount even when type 1 is not there, Kindly help.

Id   field1    field2    sum(amount)
1234 testdata1 testdata2 247.00
5123 testdata1 testdata2 147.96
5678 testdata1 testdata2 123.5
8790 testdata1 testdata2 123.5

Thanks in advance…

Answer

This sounds like conditional aggregation with filtering:

select id, field1, field2,
       sum(case when type in ('type1', 'type2') then amount else 0 end) as amount
from t
group by id, field1, field2
having sum( (type = 'type1')::int ) > 0;

This sums up the values for type1 and type2. The having clause makes sure that there is at least one row with 'type1'.

Advertisement

Answer