SQL query to sum multiple columns across multiple rows-CodePudding

I have a table in SQL Server that has multiple rows per id. There are values that need to be subtracted in two columns as well. In the sample below, I need to subtract 1033.90 - 1033.90 - 1181.60 to equal -1181.60.

ID	Value1	Value2
1	1033.90	0.00
1	0.00	1033.90
1	1181.60	0.00

I have tried a few different ways found from others' questions but nothing has worked yet. Cross Joins or Unions seemed to be the way but have yet to give the result needed. Can anyone lend any clues?

CodePudding user response：

All you seem to want is a sum(s) with a group by id

DROP TABLE IF EXISTS T;

CREATE TABLE T
(ID INT,    Value1 DECIMAL(10,2),   Value2 DECIMAL(10,2));
INSERT INTO T VALUES
(1, 1033.90 ,0.00),
(1, 0.00       ,1033.90),
(1, 1181.60 ,0.00);


SELECT ID , SUM(VALUE1) - SUM(VALUE2) AS TOT
FROM T
GROUP BY ID;

 ------ --------- 
| ID   | TOT     |
 ------ --------- 
|    1 | 1181.60 |
 ------ --------- 
1 row in set (0.001 sec)

and the group by is unnecessary if you have only 1 id. If you want a running total which where I think Lamu is coming from then you would need some way of ordering events