Calculate sum of column for selected Ids in SQL

Question

These are my tables:

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

Members can register Cart in CartRegister, and in Member.Points All points that a member earned must be calculated and inserted. So I need calculate all points of each SelectedMembers and update the Member table, but I don't know how to implement it.

The following script is in my head:

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 to 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 members be same of sum of all Members Point, maybe I need something like foreach What is your suggestion?

Answer 1

You could use a CTE (Common Table Expression) to first calculate the points for each member, and then use that inforation 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