CODEWITHSUNDEEP
sqlpivotamazon-redshiftaggregate-functions
scottdun5
scottdun5

Reputation: 23

using sums in SELECT subquery with where clause and group by

I am trying to accomplish something like the following pseudocode:

SELECT category, subcategory, 
    SUM(value WHERE type = A) as sumA, 
    SUM(value WHERE type = B) as sumB
FROM table 
GROUP BY category, subcategory

There are some category-subcategory combos that only have either Type A or Type B, and I want the other to read as null in the resulting table.

I want the final table to look like this:

category subcategory sumA sumB
A B value null
C D value value

How can I accomplish this?

Upvotes: 1

Views: 45

Answers (1)

GMB
GMB

Reputation: 222572

I would recommend conditional aggregation ; the syntax is pretty close to your pseudo-code:

select category, subcategory,
    sum(case when type = 'A' then val end) as sumA,
    sum(case when type = 'B' then val end) as sumB
from mytable
group by category, subcategory

There are some category-subcategory combos that only have either Type A or Type B, and I want the other to read as null in the resulting table.

When there is no row for a given type in a category/subcategory tuple, the sum returns null, as you expect.

Upvotes: 1

Related Questions