Sum values based on matches in other columns

Transact-SQL
1

Hey Guys,

Looking for some help I have a Table called 'cp_t' with the following columns

image

I need to add in a new column that will sum the values in Qty if it matches the value in the current row from P_ID and Material without using a group by.

the new table would look like this

image

NOTE: the above is a basic eg. the main table has around 6million rows and over 100 unique P_ID's. When queried there are usually 50k-200k rows not the full table in the results.
I have tried to do it using a Cross Join but I am fairly new to SQL I am struggling to get the sum to work with case arguments passing in a variable argument that would work on each row.

Really appreciate any and all help

2

Use the SUM windowed function. It will work best if the clustered index is P_ID, Material.

SELECT P_ID, Material, Qty
	,SUM(Qty) OVER (PARTITION BY  P_ID, Material) AS Total_Qty
FROM cp_t;
1 Like
3

Amazing thank you for your help, saved me much head scratching!!!

4

Doesn't have to be a clustered index, a nonclustered index would do just as well if it has all the needed columns in it :slight_smile: