# Sum of points and rank according to points

I am creating a game where I am calculating the user points on the basis of user activities. Everything is working fine - I got the sum of points and on the basis of points I calculated the rank. I have two columns for points calculation: `pointsup` (contains up points) and `pointsdown` (contains down points)

``````select sum(pointsup - pointsdown) as points
from table1 t1
join table2 t2
on t2.Id = t1.id
where t1.ActivityTime BETWEEN '2015-01-01' AND '2016-01-01'
group by t1.id
order by points desc
``````

Everything is fine, but the problem comes when I have to rank the users with same points. In this case, I have to consider the registration date: who registers first will come up first on the ranking. The registration date is in another table `table3`. I have tried to join this third table but the sum is behaving oddly, showing wrong sum calculation.

• It's difficult to say anything in particular of cardinality without knowing the keys for the tables involved. Can you add details about that to your question? Commented Jul 2, 2015 at 10:27
• The 3rd join is probably creating duplicates. Remove the sum and group by and you will be able to see what is causing the incorrect sums Commented Jul 2, 2015 at 10:29
• @JamesAnderson Yes thisrd joing of table is creating dupliactes but i have to get one field from third table i.e. registeration date of user, in case of same points i have to order by date of registeration Commented Jul 2, 2015 at 10:32
• Do you really call your tables `table1` and so on? Anyway, showing some example data and the wrong output (with the wrong query) could help a lot in getting an answer. Otherwise, it's just guesswork (already done by @JamesAnderson) to figure out what the odd behaviour can mean. Commented Jul 2, 2015 at 10:35
• Can you join to the third table in a statement after your 1st statement above? Commented Jul 2, 2015 at 10:35

Try this query

``````select a.*,b.points from table3 a
join(
select Sum(PointsUp-PointsDown) as points,t1.id
from table1 t1
join table2 t2 on t2.Id = t1.id
where t1.ActivityTime BETWEEN '2015-01-01' AND '2016-01-01'
Group By t1.Id)b on a.id=b.id
order by b.points desc
``````

Maybe I don't get your question right. But here is code for such a ranking. You may need to adapt it to your use-case.

``````-- Create demo data
CREATE TABLE #users(user_id int identity(1,1), points int, registration datetime)

INSERT INTO #users(registration)
VALUES  (GETDATE()), -- 1: first, because of highest points
(GETDATE()), -- 2: fourth
(GETDATE()), -- 3: third
(DATEADD(day,-1,GETDATE())) -- 4: second, as first registration

CREATE TABLE #points(user_id int, points_up int, points_down int)

INSERT INTO #points(user_id, points_up, points_down)
VALUES(1,12,2),(2,5,2),(3,11,6),(4,5,0)

SELECT u.user_id, SUM(p.points_up-p.points_down) as points, u.registration, RANK() OVER(ORDER BY SUM(p.points_up-p.points_down) DESC, registration)
FROM #users as u
INNER JOIN #points as p
ON u.user_id = p.user_id
GROUP BY u.user_id, u.registration

-- Cleanup
DROP TABLE #users
DROP TABLE #points
``````

I just use the build in ranking function based on the achieved points and if the points are equal based on the registration date.

• thanks but i have third table too that is from where i have to get a date column that i will use for ordering in case of same points Commented Jul 3, 2015 at 13:12
• Well you can add the third table too, if needed. But It depends on the table design. I don't know why your having a third one. :-) Commented Jul 3, 2015 at 13:27

Like someone said before, use it in 2 queries.

``````select t1.id, sum(pointsup - pointsdown) as points
INTO #points
from table1 t1
join table2 t2
on t2.Id = t1.id
where t1.ActivityTime BETWEEN '2015-01-01' AND '2016-01-01'
group by t1.id ;

select
DISTINCT
p.*
FROM #points p
INNER JOIN joindate j ON p.id=j.id
ORDER BY p.points, j.joindate ;
``````