CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
asmgx
asmgx

Reputation: 7984

accumulated calculation in sql server

I want to do this query on sql server

I can do this with loop but would like to know if there is any easier way that has better performance

I have table for referrals , scenario 1 : if A has 5 points and refer B that has 7 then the query should show 12 pts for A (A points + B points) scenario 2 : if A has 5 and refer to B that has 7 and A refer to C that has 3 points and B refer to D that has 4 pts and so on.. in this case A takes all points of people A + B + C + D

my table look like this

Refs

sID bigint

sName varchar(50)

sPoints int

sRefID bigint

data

Upvotes: 3

Views: 121

Answers (1)

navigator
navigator

Reputation: 1708

You can do this using recursive SQL. Try this:

With CTETable (sID, sRefID, sName, sPoints)
AS
(
SELECT Refs.sID, Refs.sRefID, Refs.sName, Refs.sPoints FROM Refs 
UNION ALL
SELECT Refs.sID, Refs.sRefID, CTETable.sName, Refs.sPoints 
FROM Refs INNER JOIN CTETable ON CTETable.sID = Refs.sRefID
)
Select sName, Sum(sPoints)
From CTETable
Group By CTETable.sName

This will yeild:

sName   TotalPoints
A   360
B   210
C   130
D   80
E   90
F   90
G   60
H   40
I   20
J   50

Upvotes: 4

Related Questions