Often in dealing with data in a normalized database, aggregate data is stored on within a parent table. This is done to simplify display of data and general querying.
However, it can cause data to become out of sync if the child tables are edited.
A typical scenario is a CRUD-based application that allows the user to manipulate data. Another scenario is where data is seeded. It is necessary to be aware of changes and aggregate the data as needed.
Long story short, I had to write a query for such a scenario where a Parent has Children and the Parent stores a total for a dollar amount that is stored on the Children. The query I came up with is this:
UPDATE p SET p.Amount = COALESCE((SELECT SUM(c.Amount) FROM [dbo].[Child] c WHERE s.ParentId = p.ParentId), 0) FROM [dbo].[Parent] p
That’s it. If the parent has no children, its amount is set to zero by the COALESCE operator. I hope you find it useful.