Sorting golf scores by total from two tables.

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 2005 Forums

Transact-SQL (2005)

Sorting golf scores by total from two tables.

Author

Topic

smcallister
Starting Member

15 Posts

Posted - 2009-06-05 : 17:14:16

I have two tables in my database:

PLAYER which has ID, NAME and HANDICAP columns

SCORE which has PLAYERID, HOLE, SCORE columns

This is for a golf game so it's pretty obvious what I need to do, but I just can't get the SQL query working.

I want to SELECT ALL PLAYERS and ORDER them by their total from the 18 entries listed in the SCORE table.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-05 : 18:11:07

[code]
select
p.NAME,
t.SCORETOTAL
from PLAYER p
join
(select PLAYERID, SUM(SCORE) as SCORETOTAL from SCORE
group by PLAYERID)t
on t.PLAYERID = p.ID
order by t.SCORETOTAL desc
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 03:42:30

quote:
Originally posted by smcallister

I have two tables in my database:

PLAYER which has ID, NAME and HANDICAP columns

SCORE which has PLAYERID, HOLE, SCORE columns

This is for a golf game so it's pretty obvious what I need to do, but I just can't get the SQL query working.

I want to SELECT ALL PLAYERS and ORDER them by their total from the 18 entries listed in the SCORE table.


SELECT NAME, TotalSCore
FROM
(
SELECT DISTINCT p.NAME,SUM(SCORE) OVER (PARTITION BY s.PLAYERID) AS TotalSCore
FROM PLAYER p
INNER JOIN SCORE s
ON s.PLAYERID=p.PLAYERID
)t
ORDER BY TotalSCore DESC

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-07 : 08:23:10

There's only one way to rock (Sammy Hagar)
but there are always many ways to query

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 13:13:14

quote:
Originally posted by webfred

There's only one way to rock (Sammy Hagar)
but there are always many ways to query

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

That is true

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources