sql server - SQL How to Update SUM of column over group in same table

Question

I have a (SQL Server) table similar to the following:

SalesSummary

Year | Team | Person | Person Sales | Team Sales/Yr

2013      1          Jim             $10                  ??

2013      1         Anna            $0                   ??

2013      2         John             $8                   ??

2013      3        Todd              $4                   ??

2013      3        Alan               $1                   ??

2014      3        Alan              $22                  ??

I'm trying to sum over this example SalesSummary table and insert the proper values into the Team Sales column. In this example, I would want $10 in the 1st and 2nd columns, $8 in the 3rd, $5 in the 4th/5th and $22 in the 6th column slot. Forgive my ignorance of SQL, but I settled on what I'm told is a poor solution as follows:

UPDATE SalesSummary SET TeamSales = sum.TeamSales
FROM (SELECT Team, Year, SUM(PersonSales) OVER (Partition By Team, Year) as TeamSales)
      FROM SalesSummary
      GROUP BY Team, Year, PersonSales
     ) AS sum, SalesSummary as SS
WHERE sum.Team = ss.Team AND sum.Year = ss.Year

I was hoping someone might be able to show be how best to perform this type of update. I appreciate any help, tips, or examples. Apologies if this is obvious.

Answer 1

Assuming you are using SQL Server, I think you want something like this:

WITH toupdate AS
     (SELECT team, year, 
             Sum(personsales) OVER (partition BY team, year) AS newTeamSales 
      FROM salessummary
     ) 
UPDATE toupdate 
   SET teamsales = newteamsales; 

Your original query has several issues and suspicious constructs. First, an aggregation subquery is not updatable. Second, you are doing an aggregation and using a window function with, although allowed, is unusual. Third, you are aggregating by PersonSales and taking the sum(). Once again, allowed, but unusual.