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?

I believe it should be `WHERE [CR].[Member_Id] = [Member].Id`. — Nikola Markovinović, Apr 23 '12 at 10:43
@NikolaMarkovinović Thanks your answer is correct can you write it as an Answer, and I can accept it. — Saeid, Apr 23 '12 at 11:27

score 7 · Answer 1 · answered Apr 23 '12 at 10:42

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

score 0 · Answer 2 · edited May 23 '17 at 11:55

0

A variation on @marc_s's solution, which is basically the same, only uses 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

edited May 23 '17 at 11:55

Community

1
1

answered Apr 23 '12 at 11:07

Andriy M

76,112
17
94
154

score 0 · Accepted Answer · answered Apr 23 '12 at 11:29

0

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  )

answered Apr 23 '12 at 11:29

Saeid

13,224
32
107
173

Calculate sum of column for selected Ids in SQL

3 Answers3