SQL Query

[zsfhaergta]

Hi, I'm not quite a sql expert, and have been having some trouble figuring this out:

I have one table with ID, Side=1 or 2, and Qty. I'd like to group by ID where sum(Qty) where Side=1 - sum(Qty) where Side=2.

So basically the difference of all Qty's having side=1 minus all Qty's having side=2.

Table:
+--------+------+------+
| ID | Side | Qty |
+--------+------+------+
| 217938 | 1 | 100 |
| 217938 | 1 | 300 |
| 217938 | 1 | 250 |
| 217938 | 2 | 275 |
| 217938 | 2 | 120 |
| 217938 | 2 | 30 |
| 217939 | 2 | 125 |
+--------+------+------+


Result:

ID Difference
217938 225
217939 -125

Anyone able to figure this out? Thanks

 

[PGO01]

Hope it helps.

SELECT A.ID, COALESCE(B.Qty, 0)-A.Qty FROM 
	(SELECT ID, SUM(Qty) AS Qty, AVG(Side) AS Side FROM #T WHERE Side = 2 GROUP BY ID, Side) AS A
		LEFT OUTER JOIN 
	(SELECT ID, SUM(Qty) AS Qty, AVG(Side) AS Side FROM #T WHERE Side = 1 GROUP BY ID, Side) AS B
		ON A.ID = B.ID

 

[PGO01]

Forgot to mention, replace #T with your table.

 

[RyanEK]

Hi,

select distinct(id),
isnull((select sum(qty) from [Table] t1 where t1.id = t.id and side = 1),0) - 
isnull((select sum(qty) from [Table] t1 where t1.id = t.id and side = 2),0) as calc
from [Table] t

Ryan

 

[zsfhaergta]

Thanks guys, I went w/ your solution PGO01

 

[markros]

select ID, Sum1 - Sum2 as difference from (select ID, sum(case when Side=1 then qty else 0 end) as Sum1, sum(case when Side=2 then qty else 0 end) as Sum2 from myTable group by ID) mySums

 

[danvlas]

select ID,sum(qty * case when side = 1 then 1 else -1 end) as TotalQty
From YourTable
Group by ID

If you could change 2 with -1 in the table, you can get rid of Case and only have sum(qty * side)
This could have the advantage of being able to cancel any entry by setting its 'side' to 0.

HTH


Daniel Vlas
Systems Consultant

 

[markros]

Hi Danvlas,

Yes, this is nice. I was only half way through with my idea and you made it much simplier.