subtract operation on two rows of temp table in SQ

Question

Age16_20    AgeBelow_16 Age21_30    Age31_40    Age41_50    Age51_60 QID

 93         81          55          46          54          48        1

 13         11          15          16          14          18        2

I want to subtract second row from first? which is best way to do inside stored procedure. performance main issue here so please provide optimal solution.

Answer 1

Assumes only 2 rows.

SELECT
    SUM(CASE WHEN ID = 2 THEN -Age16_20 ELSE Age16_20 END),
    SUM(CASE WHEN ID = 2 THEN -AgeBelow_16 ELSE AgeBelow_16 END),
    SUM(CASE WHEN ID = 2 THEN -Age21_30 ELSE Age21_30 END),
   ...etc
FROM
   Mytable

If more then 2 rows (why?) then change the CASE to

CASE ID WHEN 1 THEN Age16_20 WHEN 2 THEN -Age16_20 ELSE NULL END

Answer 2

Assuming you have a unique identifier on the rows (we'll presume it is ID) you could do something like (I have only done the first column, for brevity):

SELECT SUM(Age16_20) FROM
(SELECT Age16_20
 FROM table
 WHERE ID = 1
 UNION ALL
 SELECT -Age16_20
 FROM table
 WHERE ID = 2) Temp

Answer 3

SELECT
  [qid1].AgeBelow16 - [qid2].AgeBelow16       AS [AgeBelow16],
  [qid1].Age16_20   - [qid2].Age16_20         AS [Age16_20],
  [qid1].Age21_30   - [qid2].Age21_30         AS [Age21_30],
  [qid1].Age31_40   - [qid2].Age31_40         AS [Age31_40],
  [qid1].Age41_50   - [qid2].Age41_50         AS [Age41_50],
  [qid1].Age51_60   - [qid2].Age51_60         AS [Age51_60]
FROM
  MyTable AS [qid1]
INNER JOIN
  MyTable AS [qid2]
    ON [qid1].QID = [qid2].QID - 1
WHERE
  [qid1].QID = 1

If possible, however, you would be much better off storing the QID2 values as negatives. That way you don't need to know which one to subtract from the other; it's just a straight SUM.

SELECT
  SUM(AgeBelow16) AS [AgeBelow16],   -- (93) + (-13) = 80
  SUM(Age16_20)   AS [Age16_20],     -- (81) + (-11) = 70
  SUM(Age21_30)   AS [Age21_30],     -- (55) + (-15) = 40
  SUM(Age31_40)   AS [Age31_40],     -- (46) + (-16) = 30
  SUM(Age41_50)   AS [Age41_50],     -- (54) + (-14) = 40
  SUM(Age51_60)   AS [Age51_60]      -- (48) + (-18) = 30
FROM
  MyTable