How to calculate sum of query output

SQL Server Administration
1

Hie all,

Location Code Item No_ Zone Code Bin Code Qty Lot No_
B1 100204010006 PUT-PICK A3R5S3B3 175 sgfs
B1 100204010006 PUT-PICK A3R5S3B3 -100
B1 100204010006 PUT-PICK A3R5S3B3 -60
B1 100204010006 PUT-PICK A3R5S3B3 -1

mentioned above is my query result. I need to add one more column SUM of QTY based on Zone code and Bin Code. How do I can calculate sum of query result Qty. eg here value for sum of qty will be 14.
I am using SQL 2005

2

if you still wish to return all rows, then something like this should work using window functions:

SELECT	*,
		ZoneTotalQTY = SUM(A.QTY) OVER (PARTITION BY A.LocationCode,A.ItemNo,A.ZoneCode,A.BinCode)
FROM
(
	VALUES	('B1','100204010006','PUT-PICK','A3R5S3B3',175),
			('B1','100204010006','PUT-PICK','A3R5S3B3',-100),
			('B1','100204010006','PUT-PICK','A3R5S3B3',-60),
			('B1','100204010006','PUT-PICK','A3R5S3B3',-1)
) AS A(LocationCode,ItemNo,ZoneCode,BinCode,Qty);
1 Like