using sums in SELECT subquery with where clause and group by-CodePudding

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?

CodePudding user response：

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.