How do I get the highest sum per day for last X days?

Question

Hi All,

This is probably a easy one, but for the life of me I can't seem to figure it out.

Here is my table:

Date          User   Amount
----------    -----  ------
01/01/2010    User1  2
01/01/2010    User2  2
01/01/2010    User1  4
01/01/2010    User2  1
01/02/2010    User2  2
01/02/2010    User1  2
01/02/2010    User2  4
01/02/2010    User2  1

So on for past several months. I need get the following results:

Date          User   Amount
----------    -----  ------
01/01/2010    User1  6
01/02/2010    User2  7

Basically, the user with Max(SUM(Amount)) for each day.

I would appreciate any hints you guys can offer.

Thanks.

Answer 1

SELECT MAX(amt),`Date`,`User` FROM
    (SELECT SUM(`Amount`),`Date`,`User` as amt .... GROUP BY `Date`,`User`)
GROUP BY `Date`

Answer 2

select t.*
from (
    select Date, Max(Amount) as MaxAmount
    from MyTable
    group by Date           
) tm
inner join MyTable t on tm.Date = t.Date and tm.MaxAmount = t.Amount

Note: this will give you both user records if there are two users with the same max amount on a given day.

Answer 3

I actually ended up going with the following:

WITH ranked AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY SUM(Amount), Date, User) as 'rank', SUM(Amount) AS Amount, User, Date FROM MyTable GROUP BY Date, User
)
SELECT Date, User, Amount
FROM ranked
WHERE rank IN ( select MAX(rank) from ranked group by Date)
ORDER BY Date DESC

Answer 4

Can be less verbose with the RANK ... OVER, but following is the straight-forward solution:

WITH summary_user_date 
AS (SELECT      Date, User, SUM(Amount) AS SumAmount
    FROM        MyTable
    GROUP BY    Date, User
)
,   summary_date
AS (SELECT      Date, MAX(SumAmount) AS SumAmount
    FROM        summary_user_date 
    GROUP BY    Date
)
SELECT      summary_user_date.*
FROM        summary_user_date 
INNER JOIN  summary_date
        ON  summary_date.Date = summary_user_date.Date
        AND summary_date.SumAmount = summary_user_date.SumAmount

It should be mentioned that if more then one user has the same maximum amount, all of them will be shown. If this is not desired then one should use RANK based solution.

Answer 5

Using CTEs you could do something like:

With DailyTotals As
    (
    Select [Date], [User], Sum(Amount) As Total
    From #Test
    Group By [Date], [User]
    )
Select [Date],[User],Total
From DailyTotals As DT
Where Total = (
                Select Max(Total)
                From DailyTotals As DT1
                Where DT1.[Date] = DT.[Date]
                )
Order By DT.[Date]

A non-CTE solution would be:

Select [Date],[User],Total
From    (
        Select [Date], [User], Sum(Amount) As Total
        From #Test
        Group By [Date], [User]
        ) As DT
Where DT.Total =    (
                    Select Max(DT1.Total)
                    From    (       
                            Select [Date], [User], Sum(Amount) As Total
                            From #Test
                            Group By [Date], [User]
                            ) As DT1
                    Where DT1.[Date] = DT.[Date]
                    )
Order By DT.[Date]