'SQL conditional addition or subtraction

I need an aggregate sum for a table column (grouped by other columns), but with some quantities subtracted rather than added, depending on the value in another column (not in the group or results). Consider the following example:

CREATE TABLE Activity (
    wYear SmallInt,
    yMonth SmallInt,
    Account Int,
    Quantity Int,
    Adjustment VarChar(128)
);

INSERT INTO Activity (wYear, yMonth, Account, Quantity, Adjustment)
  VALUES
(2008, 9, 12345, 1000, 'add'),
(2008, 9, 12345, 5000, 'add'),
(2008, 9, 12345, 4000, 'sub'),
(2007, 5, 54321, 1500, 'add')
;

I'm looking to total the Quantity column for each combination of wYear, yMonth, Account, but the quantity should be subtracted (rather than added) if the adjustment is "sub". Summing the quantities for the "add" value is simple:

SELECT  wYear, yMonth, Account,  SUM(Quantity) as "Totals"
  FROM  myTable
  WHERE Adjustment = 'add'
  GROUP BY wYear, yMonth, Account
;

How do I also subtract quantities for the "sub" adjustments?

Desired results for the sample:

wYear yMonth Account Totals
2008 9 12345 2000
2007 5 54321 1500
sql


Solution 1:[1]

SELECT wYear, yMonth, Account, SUM(case when Adjustment  = 'add' then
Quantity when Adjustment = 'sub' then -Quantity else 0 end) as "Totals"
FROM Activity

GROUP BY wYear, yMonth, Account

Test friendly version

Declare @myTable table (wYear int, ymonth int, Account varchar(20), 
Quantity int, Adjustment varchar(20))

Insert into @mytable values (2008, 9, '12345', 1000, 'add')
Insert into @mytable values (2008, 9, '12345', 5000, 'add')
Insert into @mytable values (2008, 9, '12345', 4000, 'sub')
Insert into @mytable values (2007, 5, '54321', 1500, 'add')
 
SELECT wYear, yMonth, Account, SUM(case when Adjustment  = 'add' then
Quantity when Adjustment = 'sub' then -Quantity else 0 end) as "Totals"
FROM @myTable

GROUP BY wYear, yMonth, Account


wYear   yMonth  Account Totals
2007    5   54321   1500
2008    9   12345   2000

Solution 2:[2]

I hope this is what you want:

SELECT 
    adds.wYear, 
    adds.yMonth, 
    adds.Account, 
    totals_add - totals_sub AS totals
FROM
(
    SELECT wYear, yMonth, Account, SUM(Quantity) as "totals_add"
    FROM Activity 
    WHERE Adjustment = 'add'
    GROUP BY wYear, yMonth, Account
) adds
INNER JOIN 
(
    SELECT wYear, yMonth, Account, SUM(Quantity) as "totals_sub"
    FROM Activity 
    WHERE Adjustment = 'sub'
    GROUP BY wYear, yMonth, Account
) subs
ON 
     adds.wYear = subs.wYear 
     AND adds.yMonth = subs.yMonth 
     AND adds.Account = subs.Account

Solution 3:[3]

I doubt its efficiency but this should do the trick

SELECT 
    wYear, yMonth, Account, 
    (
      (SELECT SUM(Quantity) FROM myTable AS m 
        WHERE m.wYear=wYear AND 
              m.yMonth=yMonth AND 
              m.Account=Account AND 
              m.Adjustment='Add') -
      (SELECT SUM(Quantity) FROM myTable AS m 
        WHERE m.wYear=wYear AND 
              m.yMonth=yMonth AND 
              m.Account=Account AND 
              m.Adjustment='Sub')
   ) as "Totals"
FROM Activity 
GROUP BY wYear, yMonth, Account

Solution 4:[4]

Maybe this could work -

SELECT wYear, yMonth, Account, 
SUM(case when Adjustment = 'sub' then Quantity*-1 else Quantity end) as "Totals"
FROM Activity 
WHERE Adjustment = 'add'
GROUP BY wYear, yMonth, Account

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 outis
Solution 2 outis
Solution 3 outis
Solution 4 outis

Related Questions