Calculate column sum for selected identifiers in SQL

Question

These are my tables:

• Member : Id, Points
• CartRegister : Id, Member_Id, CartId, RegisterDate, Point
• SelectetMembers : Id, Member_Id

Participants can register the cart in CartRegister , and in Member.Points All points earned by a member must be calculated and inserted. Therefore, I need to calculate all the points of each SelectedMembers and update the Member table, but I do not know how to implement it.

In my head is a script:

 UPDATE [Member] SET [Points]= ( SELECT SUM([CR].[Point]) AS [AllPoints] FROM [CartRegister] AS [CR] WHERE [CR].[Member_Id] = --??? ) WHERE [Members].[Member].[Id] IN ( SELECT Member_Id From SelectedMembers ) 

So, I am confused by what is the where clause in Select Sum(Point) if I use

  WHERE [CR].[Member_Id] IN ( Select Member_Id From SelectedMembers ) 

Then the sum of all the members will be the same as the sum of all the members of Point, maybe I need something like foreach What is your suggestion?

Answer 1

Check this:

 UPDATE [Member] SET [Points]= ( SELECT SUM([CR].[Point]) AS [AllPoints] FROM [CartRegister] AS [CR] WHERE [CR].[Member_Id] = [Member].[Id] ) WHERE [Members].[Member].[Id] IN ( SELECT Member_Id From SelectedMembers ) 

Answer 2

You can use CTE (Common Table Expression) to first calculate points for each member, and then use this application to update the Members table:

 -- this CTE takes all selected members, and calculates their total of points ;WITH MemberPoints AS ( SELECT Member_ID, AllPoints = SUM(Point) FROM CartRegister WHERE Member_ID IN (SELECT ID FROM SelectedMembers) GROUP BY Member_ID ) UPDATE dbo.Member SET Points = mp.AllPoints FROM MemberPoints mp WHERE dbo.Member.Member_ID = mp.Member_ID 

Answer 3

The @marc_s solution variant, which is basically the same, uses only a slightly different syntax:

 WITH aggregated AS ( SELECT *, AllPoints = SUM(Point) OVER (PARTITION BY Member_ID) FROM CartRegister WHERE Member_ID IN (SELECT ID FROM SelectedMembers) ) UPDATE aggregated SET Points = AllPoints