SQLTeam.com | Weblogs | Forums

Sum based on conditions

I have the following SQL query with which I want to update a column,ton in a table UniekeBlokkeHistories. This value is the sum of all the values of column net in table wbridge_history where grower,block,section and oesjaar are the same.

UPDATE UniekeBlokkeHistories
SET ton = (SELECT SUM(t2.net)
FROM wbridge_history t2
WHERE t2.grower = grower
and t2.[block] = [block]
and t2.section = section
and t2.oesjaar = oesjaar )

However, it adds all the values in net and not based on the set of conditions. What am I missing?

Thank you in advance.

;WITH cte AS
(
	SELECT
		grower,
		[block],
		[section],
		oesjaar,
		new_ton = SUM(t2.net)
	FROM
		wbridge_history t2
	GROUP BY 
		grower,
		[block],
		[section],
		oesjaa
)
UPDATE u 
	SET [ton] = new_ton
FROM
	UniekeBlokkeHistories u
	INNER JOIN cte c ON	
		c.grower = u.grower
        AND c.[block] = u.[block]
        AND c.section = u.section
        AND c.oesjaar = u.oesjaar;
UPDATE UBH
SET ton = wh.total_net
FROM UniekeBlokkeHistories UBH
INNER JOIN (
    SELECT grower, block, section, oesjaar, SUM(net) AS total_net
    FROM wbridge_history
    GROUP BY grower, block, section, oesjaar
) AS wh ON
    wh.grower = UBH.grower
    and wh.[block] = UBH.[block]
    and wh.section = UBH.section
    and wh.oesjaar = UBH.oesjaar

Thank you James and Scott. Both solutions works.