SQLTeam.com | Weblogs | Forums

Subtract values of a given reoccuring ID


#1

Hi all, I have a problem I have been breaking my brain with. Given the following table;

ID1 2
ID2 2
ID3 3
ID1 4
ID2 5
ID3 8

What I want to do is subtract the numeric values based on the identical IDs. so 4 - 2, 5 - 2, 8 - 3. And put them in another table. so resulting table should be

ID1 2
ID2 3
ID3 5

Thank you.


#2

--INSERT INTO dbo.another_table
SELECT ID1, MAX(value) - MIN(value) AS value
FROM dbo.table_name
GROUP BY ID1
HAVING COUNT(*) = 2