SQLTeam.com | Weblogs | Forums

How to: case add column rows, if another column row equals previous row?

Hello,

col1 col2 col3
A x. 5
A x. 6
B x. 3
C x. 3
C x. 0
C x. 11
D x. 67
D x. 54
D x. 5
D x. 99

How do I get the sum in col4
for col3, only where col1 are the same please?

Eg:

col1 col2 col3 col4
A x. 5 5
A x. 6 11
B x. 3 3
C x. 3 6
C x. 0 6
C x. 11 17
D x. 67 67
D x. 54 121
D x. 5 126
D x. 99 225

Thanks in advance!

this looks very easy to do

but please explain in a better way ..not able to understand

image

Looks like your in need of a running total,

SELECT 
	Col1,
	Col2,
	Col3,
	SUM(Col3) OVER (PARTITION BY Col1 ORDER BY Col1, Col2, Col3) AS RunningTotal
FROM
	(
	SELECT 'A' AS Col1, 'x.' AS Col2, 5 AS Col3
	UNION 
	SELECT 'A', 'x.', 6
	UNION 
	SELECT 'B', 'x.', 3
	UNION 
	SELECT 'C', 'x.', 3
	UNION 
	SELECT 'C', 'x.', 0
	UNION 
	SELECT 'C', 'x.', 11
	UNION 
	SELECT 'D', 'x.', 67
	UNION 
	SELECT 'D', 'x.', 54
	UNION 
	SELECT 'D', 'x.', 5
	UNION 
	SELECT 'D', 'x.', 99
	) tbl

OVER Clause (Transact-SQL) - SQL Server | Microsoft Docs

1 Like