Add a new column that will sum a specific column-CodePudding

dbo.table1

DataID MemberID
2        1001
3         1001

dbo.table2

PointsID MemberID  PartnerPoints
1         1001     100
2         1001     100

dbo.table3

DataID table3ID PointsID    PartnerPoints
2        1        1             100
3        2        2             100

I need to add a new column on each table that will make the sum of partnerPoints column in dbo.table3 to 0.

Expected output:

dbo.table1

DataID MemberID
2        1001
3        1001
4        1001

dbo.table2

PointsID MemberID  PartnerPoints
1         1001     100
2         1001     100
3         1001     -200

dbo.table3

DataID table3ID PointsID    PartnerPoints
2        1        1             100
3        2        2             100
4        3        3             -200

I tried this

INSERT INTO [dbo].[Table3]
(
    DataID
    PointsID
    PartnerPoints,
)
SELECT
   DataID
   PointsID
   -PartnerPoints,
FROM @tempTable t
    INNER JOIN dbo.table2 e ON t.memberID = e.MemberID 
WHERE e.PartnerPoints <> 0

instead of adding only one row in dbo.table3 it add 2 columns:

dbo.table3

DataID table3ID  PointsID    PartnerPoints
2        1        1             100
3        2        2             100
4        3        3             -100
4        4        3             -100

What part in the code do I need to improve? Using group by does not give the expected output.

CodePudding user response：

Thanks for your help.

I tried this.

INSERT INTO [dbo].[Table3]
(
    DataID
    PointsID
    PartnerPoints,
)
SELECT
   MAX(DataID)
   MAX(PointsID)
   -SUM(PartnerPoints),
FROM @tempTable t
    INNER JOIN dbo.table2 e ON t.memberID = e.MemberID 
WHERE e.PartnerPoints <> 0

output: dbo.table3

DataID table3ID PointsID    PartnerPoints
2        1        1             100
3        2        2             100
4        3        3             -200

I don't used group by since it will only group it by pointsID and dataID which results to inserting of 2 new rows in dbo.Table3 instead of 1 row.

CodePudding user response：

Something like:

INSERT INTO [dbo].[Table3]
(
    DataID
    PointsID
    PartnerPoints,
)
SELECT
   DataID
   PointsID
   SUM(-PartnerPoints),
FROM @tempTable t
    INNER JOIN dbo.table2 e ON t.memberID = e.MemberID 
WHERE e.PartnerPoints <> 0
group by DataID,PointsID