How to sum a certain column based on other columns values

user_11025776 0
Viewed 41

I have a table with multiple work_weeks, product_names, and event_names. Here's an example:

work_week     product_name  event_name         num_products         attributeA      attributeB
 2021-17         A            Event1               25                  Y                X         
 2021-17         A            Event1               20                  Y                Z
 2021-17         A            Event1               15                  N                Y

I'm trying to perform a 'group by' on this table, so I can get the sum of num_products grouped by work_week, product_name, and event_name. However, in my 'group by' table, I need several versions of sum(num_products) depending on the values of attributeA and attributeB. Something like this:

work_week     product_name  event_name     sum_when_A = N     sum_when_A = Y and B = Z
  2021-17         A            Event1           15                      20

Any ideas on how to execute this?

sql-server sql
2 Answers

Just use conditional aggregation:

select work_week, product_name, event_name,
       sum(case when attributeA = 'N' then num_products else 0 end) as sum_n,
       sum(case when attributeA = 'Y' and attributeB = 'Z' then num_products else 0 end) as num_yz
from t
group by work_week, product_name, event_name;
user_11440350

Use conditional aggregation

select work_week, product_name, event_name
, sum(iif(attributeA = 'N', num_products, 0))
, sum(iif(attributeA = 'Y' and attributeB = 'Z', num_products, 0))
from myTable
group by work_week, product_name, event_name
user_10170650
Related