I have a table where I have data from sales.
The structure is

When the Action is 0 then the items not count on Sum.
When the Action is 1 then the items are count on Sum.
When the Action is 2 then the items are substruct from the Sum.

So, I want to make a Sum on Items based on Action field.

The correct result must be 5 Items on product a1 and 1 Item for the product a2.

Do you have any ideas on how can I do this?

CodePudding user response：

You can use a case expression in combination with aggregation (group by, sum):

select   ProductID,
         sum(case Action
                 when 2 then Items
                 when 3 then -Items
                        else 0
             end) SumItems
from     Sales
group by ProductID;

CodePudding user response：

you can try something like this

with x (ProductID,Items,Price,Action)
as
(
  Select           'a1','10','100','1'
  union all Select 'a1','6','60','0'
  union all Select 'a1','5','50','2'
  union all Select 'a2','3','30','1'
  union all Select 'a2','4','40','0'
  union all Select 'a2','1','70','2'
)
Select 
y.ProductID,
sum(y.[Count]) as sum
from
    (
    Select 
    *,
    case when Action = 0 then 0 when Action = 1 then items when  Action = 2 then  -1*items end as Count
    from x
    ) y
GROUP by 
y.ProductID