somewhat complex query
-
I have a table of data like so:
PlayerName varchar
Location_ID varchar
Season_ID varchar
Points int
PowerRank dec
finish int
Each player can appear at a location multiple times within a season. He earns points each time and has a finish of anywhere from 1-10.
My query needs to return a table displaying the following:
Rank
Player Name
Location Bankroll (sum of all the player's points at that location)
Games (Number of times the player plays at the location)
Final Tables( Number of times the player finishes 1-9)
FinalTable%( Number of Final Tables/Number of games played)
Wins (Number of times player finishes 1st)
PowerRank( Player ranking that is calculated from all the players results at other locations as well)
Here is what I have so far:
SELECT player_id, rank() over(order by sum(points)desc) as Rank, sum(points) as BankRoll,
count(*) as Games
FROM t_score
WHERE Location_id = 'Gra_up' and season_id=11
GROUP BY player_id
ORDER BY sum(points) desc
That gives me the PlayerID, their rank, their bankroll(total points) and the number of games played. What I cannot figure out is how to get the other calculations with the correct syntax . Can anyone help on this one?
-
It's kind of hard to tell on some of the things, but here would be a couple of them.
Sum(case when finish betweeen 1 and 9 then 1 else null end) as FinalTables,Sum(case when finish betweeen 1 and 9 then 1 else null end)/count(*) as FinalTablepercent,sum(case when finish=1 then 1 else 0 end) as Wins----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Thanks Matt for the help
I am getting this error message:
Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near 'betweeen'.
my query looks like this now:
SELECT player_id, rank() over(order by sum(points)desc) as Rank, sum(points) as BankRoll,
count(*) as Games,
sum(case when finish betweeen 1 and 9 then 1 else null end) as FinalTables,
sum(case when finish betweeen 1 and 9 then 1 else null end)/count(*) as FinalTablepercent,
sum(case when finish=1 then 1 else 0 end) as Wins
FROM t_score
WHERE Location_id = 'Gra_up' and season_id=11
GROUP BY player_id
ORDER BY sum(points) desc
-
too funny
turns out 'between' has only 3 'e's not 4 lol
Thanks again for the heelp
works like a charm -
Between has only 3 "e".
Correct spelling and try again.
N 56°04'39.16"
E 12°55'05.25" -
That's what I get for freehanding it....:D
Glad it helped, typo notwithstanding....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply