SQL Add a total column

I have a table as follows

StockId                      Product                                                  Quant   Cancelled
381847      Dry King Scallops 4x2kg XXL Ecosse FAO27      30.0000       True
381845      Dry King Scallop 4x2kg -Ecosse FAO 27              40.0000       True
381845      Dry King Scallop 4x2kg -Ecosse FAO 27                5.0000       False
381845      Dry King Scallop 4x2kg -Ecosse FAO 27                7.0000       False
381847      Dry King Scallops 4x2kg XXL Ecosse FAO27        5.0000       False

I am looking to query this table and add another column for the total items for each stockId - e.g. for each row beginning 381847 it should be 5 if the query excludes cancelled lines or 35 if it includes them and for each line beginning 381845 it should be 12 or 52.

I could write a program to do this, but I think that it should be possible with a query.
Any ideas?
grwallaceAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
First of all: You cannot calculate a running total without order.

Then: What SQL dialect?

Here's a solution in T-SQL (SQL Server 2012+):

DECLARE @Sample TABLE
	(
		RN INT,
		StockId INT ,
		Product NVARCHAR(255) ,
		Quant DECIMAL(18, 4) ,
		Cancelled BIT
	);

INSERT  INTO @Sample
VALUES  ( 1, 381847, 'Dry King Scallops 4x2kg XXL Ecosse FAO27', 30.0000, 1 ),
	( 2, 381845, 'Dry King Scallop 4x2kg -Ecosse FAO 27   ', 40.0000, 1 ),
	( 3, 381845, 'Dry King Scallop 4x2kg -Ecosse FAO 27   ', 5.0000, 0 ),
	( 4, 381845, 'Dry King Scallop 4x2kg -Ecosse FAO 27   ', 7.0000, 0 ),
	( 5, 381847, 'Dry King Scallops 4x2kg XXL Ecosse FAO27', 5.0000, 0 );

SELECT  *,
	SUM(S.Quant) OVER (PARTITION BY S.StockId ORDER BY S.RN ),
	SUM(IIF(S.Cancelled = 0, S.Quant, 0)) OVER (PARTITION BY S.StockId ORDER BY S.RN ),
	SUM(IIF(S.Cancelled = 1, S.Quant, 0)) OVER (PARTITION BY S.StockId ORDER BY S.RN )
FROM    @Sample S
ORDER BY S.RN;

Open in new window

0
 
grwallaceAuthor Commented:
That will do nicely - thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.