SQL conditonal additon or subtraction

Question

I have one table in which I need to do some simple math on a field. I have attempted nested SELECTS, IF/THEN, or CASE staments within SQL to no avail.

EXAMPLE TABLE:
wYear, yMonth, Account, Usage, Serv_code
2008, 9, 12345, 1000, Banana
2008, 9, 12345, 5000, Banana
2008, 9, 12345, 4000, Apple
2007, 5, 54321, 1500, Banana

I need to Group By wYear, yMonth, Account and add up "Usage" WHERE Serv_code = 'Banana'...

SELECT wYear, yMonth, Account, SUM(Usage) as "Totals"
FROM myTable
WHERE Serv_code = 'Banana'
GROUP BY wYear, yMonth, Account

BUT where I hit the wall is - If "Serv_code" does equal 'Apple' (WHERE Serv_code = 'Apple'), I need to subtract the 'Apple' Usage from the SUM(Usage).

EXAMPLE RESULTS FROM USING THE TABLE ABOVE:
2008, 9, 12345, 2000
2007, 5, 54321, 1500

Any help would be much appreciated. (I am a hack when it comes to SQL and the answer is plain as day, buts its been one of those days.)

Answer 1

I hope this is what you want:

SELECT 
    banana.wYear, 
    banana.yMonth, 
    banana.Account, 
    totals_banana - totals_apple AS totals
FROM
(
    SELECT wYear, yMonth, Account, SUM(Usage) as "totals_banana"
    FROM myTable 
    WHERE Serv_code = 'Banana'
    GROUP BY wYear, yMonth, Account
) banana
INNER JOIN 
(
    SELECT wYear, yMonth, Account, SUM(Usage) as "totals_apple"
    FROM myTable 
    WHERE Serv_code = 'Apple'
    GROUP BY wYear, yMonth, Account
) apple 
ON 
     banana.wYear = apple.wYear 
     AND banana.yMonth = apple.yMonth 
     AND banana.Account = apple.Account

Answer 2

SELECT wYear, yMonth, Account, SUM(case when Serv_code  = 'Banana' then
Usage when Serv_Code = 'Apple' then -Usage else 0 end) as "Totals"
FROM myTable

GROUP BY wYear, yMonth, Account

Test friendly version

Declare @myTable table (wYear int, ymonth int, Account varchar(20), 
Usage int, Serv_Code varchar(20))

Insert into @mytable values (2008, 9, '12345', 1000, 'Banana')
Insert into @mytable values (2008, 9, '12345', 5000, 'Banana')
Insert into @mytable values (2008, 9, '12345', 4000, 'Apple')
Insert into @mytable values (2007, 5, '54321', 1500, 'Banana')

SELECT wYear, yMonth, Account, SUM(case when Serv_code  = 'Banana' then
Usage when Serv_Code = 'Apple' then -Usage else 0 end) as "Totals"
FROM @myTable

GROUP BY wYear, yMonth, Account


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

Answer 3

You could use a case statement to get a positive or negative value depending on the value in Serv_code:

select wYear, yMonth, Account, sum(Usage * case Serv_code when 'Apple' then -1 else 1 end) as "Totals"
from myTable
where Serv_code in ('Banana', 'Apple')
group by wYear, yMonth, Account

Answer 4

I doubt its efficiency but this should do the trick

SELECT 
    wYear, yMonth, Account, 
    (
      (SELECT SUM(Usage) FROM myTable AS m 
        WHERE m.wYear=wYear AND 
              m.yMonth=yMonth AND 
              m.Account=Account AND 
              m.Serv_code='Banana') -
      (SELECT SUM(Usage) FROM myTable AS m 
        WHERE m.wYear=wYear AND 
              m.yMonth=yMonth AND 
              m.Account=Account AND 
              m.Serv_code='Apple')
   ) as "Totals"
FROM myTable 
GROUP BY wYear, yMonth, Account

Answer 5

Maybe this could work -

SELECT wYear, yMonth, Account, SUM(case when Usage = 'Apple' then usage*-1 else Usage end) as "Totals" FROM myTable WHERE Serv_code = 'Banana' GROUP BY wYear, yMonth, Account