how to substract two sums based on different field values in one table (view)

Question

example: i need the numbers of which the sum of the amount where id = 1 is not equal of the sum of the amount where id <> 1, together with the id's and difference.
The table (view in this case) may look like this:

NUMBER   AMOUNT   ID

0001     500       1
0001     500       2
0002     400       3
0003     299       1
0003     300       3
0003     300       3

Many thanks for your help on this one.

Answer 1

Using this query you can get sum of amounts grouped by number, with ID equals 1 and ID not equals 1.

SELECT NUMBER
, SUM(CASE WHEN ID = 1 THEN AMOUNT ELSE 0 END) AS Equals1
, SUM(CASE WHEN ID <> 1 THEN AMOUNT ELSE 0 END) AS NotEquals1
FROM DataTable
GROUP BY NUMBER 

If this is you expected, use following query to get difference amounts grouped by number.

SELECT NUMBER, (Equals1 - NotEquals1) AS DifferenceAmount
FROM
(
    SELECT NUMBER
    , SUM(CASE WHEN ID = 1 THEN AMOUNT ELSE 0 END) AS Equals1
    , SUM(CASE WHEN ID <> 1 THEN AMOUNT ELSE 0 END) AS NotEquals1
    FROM DataTable
    GROUP BY NUMBER
) AS GroupedTable
WHERE Equals1 <> NotEquals1