CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
CodeNinja
CodeNinja

Reputation: 3278

sql query to subtract columns

I have a table which looks like this : 

  Item  Month    Year    Sales
Name1      1     2013     333
Name2      2     2013     454
Name3      1     2013     434

I need to write a stored procedure which looks like this : 

Item   Sales_On_Month(1)   Sales_On_Month(2)   Sales_On_Month(2)-Sales_On_Month(1)  Sales_On_Month(3) Sales_On_Month(3)-Sales_On_Month(2)
Name1           333                334                              1                        335             1
Name2           454                454                              0                        654             200

I tried the following query : I see a lot of nulls in the middle.If you could let me know the modifications to the query or another approach it would be great :

select  (case when [MONTH] = 1 then Sales  END) AS Sales_On_Month(1),
 (case when [MONTH] = 2 then Sales  END) AS Sales_On_Month(2),
 (case when [MONTH] = 2 then Sales  END) - (case when [MONTH] = 1 then Sales  END) AS  Sales_On_Month(2)-Sales_On_Month(1) ...............
from ABC;

Upvotes: 0

Views: 216

Answers (1)

Hart CO
Hart CO

Reputation: 34774

Use an aggregate, SUM(), MAX(), whatever, andGROUP BY`:

SELECT Item
      ,SUM(CASE WHEN [MONTH] = 1 THEN Sales END) AS Sales_1
      ,SUM(CASE WHEN  [MONTH] = 2 THEN Sales END) AS Sales_2
      ,SUM(CASE WHEN  [MONTH] = 2 THEN Sales END) - SUM(CASE WHEN [MONTH] = 1 THEN Sales END) AS  Sales_On_Month(2)-Sales_On_Month(1)
FROM ABC
GROUP BY Item

Upvotes: 3

Related Questions