Hello I have query which is pulling the winnings for each player by month and year, I total the winnings but I would only like to see the top winner for each month.
Here is the query I have, any insight would be great thanks in advance.
SELECT TOP (100) PERCENT dbo.Players.Player_name, SUM(dbo.Event_data.Transaction_value) AS Winnings, DATEPART(year, dbo.Events.starttime) AS Year,
DATEPART(month, dbo.Events.starttime) AS Month
FROM dbo.Event_data INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.id
GROUP BY dbo.Players.Player_name, dbo.Event_data.Transaction_type, DATEPART(year, dbo.Events.starttime), DATEPART(month, dbo.Events.starttime)
HAVING (dbo.Event_data.Transaction_type = 1)
ORDER BY Year, Month, Winnings DESC
Hi.
If you want to get the maximum value for each month, you must use the MAX function instead of SUM.
If you want the maximum for each month but, wants the total too, you can't see them in the same query because for getting the total you need to group the months too. May you coudl get the maximum for ecah month in the .net code:
Dim rw() As DataRow
rw = dataTable.Select("MAX(Winnings) AND Month = 1")
|||
Well first you could put this data you have in the above querry of the totals of all players per month in a table:
Table tempPlayerData
(
Player name,
PlayerScore,
Month etc...
)
then get the max from this temp table for each month.
|||Sorry can you provide your answer using the query I provided.
thanks
|||Sorry can you provide your answer using the query I provided.
thanks
|||You want it in separate query or same query ?
So how will you want the result like below ?
[Player_Name] - [Winnings] - [Year] - [Month] - [Top Winner Name]
Note the [Top Winner Name] will be repeated for every record
In the end I would like
Player name Winnings Month Year
george 24,000 1 2007
fred 12,000 2 2007
etc
only one record for each month
