Select DISTINCT SUM on nested table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select DISTINCT SUM on nested table

Author

Topic

JohnSourcer
Starting Member

9 Posts

Posted - 2012-08-02 : 06:13:54

Hi Gurus,

Needing a bit of a pointer on this query:

Table Users:

UserId | OtherPoints
--------------------
1 | 10
2 | 20

Table Challenges:

ChallengeId | UserId | ChallengePoints
--------------------------------------
1 | 1 | 500
1 | 1 | 770
2 | 1 | 200

I want to get the Top 3 Users and their TotalPoints where TotalPoints = OtherPoints + SUM(DISTINCT ChallengePoints)

I've tried several different approaches including nested tables but I don't think they're efficient.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-02 : 07:00:30

Would this work for you?

SELECT
	u.UserId,
	u.OtherPoints + s.ChallengePoints AS TotalPoints
FROM
	Users u
	OUTER APPLY
	(
		SELECT SUM(DISTINCT ChallengePoints) AS ChallengePoints
		FROM Challenges c
		WHERE c.UserId = u.UserId
	) s;

JohnSourcer
Starting Member

9 Posts

Posted - 2012-08-02 : 08:33:49

Thank-you muchly, Sunitabeck. Pointed me in the right direction ended up with:

SELECT
	u.UserId,
	u.OtherPoints + ISNULL(SUM(s.ChallengePoints), 0) AS TotalPoints
FROM
	Graduates u
	OUTER APPLY
	(
		SELECT DISTINCT c.UserId, c.ChallengeId, ISNULL(MAX(c.ChallengePoints), 0) AS ChallengePoints FROM Challenges c WHERE c.UserId = u.UserId GROUP BY UserId, ChallengeId
	) s  GROUP BY u.UserId, u.OtherPoints ORDER BY TotalPoints DESC;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-02 : 09:59:11

quote:

Originally posted by JohnSourcer

Thank-you muchly, Sunitabeck. Pointed me in the right direction ended up with:

SELECT
	u.UserId,
	u.OtherPoints + ISNULL(SUM(s.ChallengePoints), 0) AS TotalPoints
FROM
	Graduates u
	OUTER APPLY
	(
		SELECT DISTINCT c.UserId, c.ChallengeId, ISNULL(MAX(c.ChallengePoints), 0) AS ChallengePoints FROM Challenges c WHERE c.UserId = u.UserId GROUP BY UserId, ChallengeId
	) s  GROUP BY u.UserId, u.OtherPoints ORDER BY TotalPoints DESC;

you dont need DISTINCT inside as you're already grouping on the columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources