CODEWITHSUNDEEP
sqlsql-servert-sqlsummax
Panos B.
Panos B.

Reputation: 49

Showing the Max values of a Sum

i am trying to find the max values of a sum result but i can not find a way to show the date too. i have a table transactions which has data like this:

transaction_date--Transaction_agent_id--transaction_amount
2/5/11                         321      20
2/5/11                         321      10
2/5/11                         400      100
3/5/11                         321      5
3/5/11                         345      100
3/5/11                         321      10
3/5/11                         345      50
4/5/11                         345      80
4/5/11                         321      5

what i want the result to be is this

transaction_date--Transaction_agent_id--transaction_amount
    2/5/11                       321     30
    2/5/11                       400    100
    3/5/11                       345    150

What it should do is first of all to sum all the amount per agent_id, per day and then to show me the best day of that agent.

What i have done is this

SELECT a.transaction_agent_id, max(a.stotal) FROM
(
SELECT transaction_date,transaction_agent_id, sum(transaction_amount) as 'Stotal' FROM transactions
GROUP BY transaction_agent_id, transaction_date
)a
GROUP BY a.transaction_agent_id

which gives me this

Transaction_agent_id--transaction_amount
               321     30
               400    100
               345    150

which is correct but i can not add the transaction_date.

Update: I just solved it. Well if anyone wants the solution here it is.

SELECT  a.transaction_date, a.transaction_agent_id, max(a.stotal) FROM
(
  SELECT row_number() over (partition by transaction_agent_id order by sum(transaction_amount)desc) AS 'roww' ,transaction_date,transaction_agent_id, sum(transaction_amount) as 'Stotal' FROM transactions
  GROUP BY transaction_agent_id, transaction_date
) a

WHERE a.roww = 1
GROUP BY a.transaction_date, a.transaction_agent_id, a.roww

Thank you all for your input.

Upvotes: 0

Views: 287

Answers (2)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

SQL Statement

;WITH Max_Transaction_Amount AS (
    SELECT  Transaction_Date
            , Transaction_Agent_ID
            , Transaction_Amount = SUM(Transaction_Amount)
    FROM    q
    GROUP BY
            Transaction_Date
            , Transaction_Agent_ID
)
SELECT  mta.Transaction_Date
        , mta.Transaction_Agent_ID
        , mta.Transaction_Amount
FROM    Max_Transaction_Amount mta 
        INNER JOIN (
            SELECT  Transaction_Agent_ID
                    , Transaction_Amount = MAX(Transaction_Amount)
            FROM    Max_Transaction_Amount
            GROUP BY
                    Transaction_Agent_ID        
        ) q ON  mta.Transaction_Agent_ID = q.Transaction_Agent_ID
                AND mta.Transaction_Amount = q.Transaction_Amount

Test script

/* Create test data */
;WITH q (Transaction_Date, Transaction_Agent_ID, Transaction_Amount) AS (
  SELECT '2/5/11',321,20
  UNION ALL SELECT '2/5/11',321,10
  UNION ALL SELECT '2/5/11',400,100
  UNION ALL SELECT '3/5/11',321,5
  UNION ALL SELECT '3/5/11',345,100
  UNION ALL SELECT '3/5/11',321,10
  UNION ALL SELECT '3/5/11',345,50
  UNION ALL SELECT '4/5/11',345,80
  UNION ALL SELECT '4/5/11',321,5
)
/* Actual query */
, Max_Transaction_Amount AS (
    SELECT  Transaction_Date
            , Transaction_Agent_ID
            , Transaction_Amount = SUM(Transaction_Amount)
    FROM    q
    GROUP BY
            Transaction_Date
            , Transaction_Agent_ID
)
SELECT  mta.Transaction_Date
        , mta.Transaction_Agent_ID
        , mta.Transaction_Amount
FROM    Max_Transaction_Amount mta 
        INNER JOIN (
            SELECT  Transaction_Agent_ID
                    , Transaction_Amount = MAX(Transaction_Amount)
            FROM    Max_Transaction_Amount
            GROUP BY
                    Transaction_Agent_ID        
        ) q ON  mta.Transaction_Agent_ID = q.Transaction_Agent_ID
                AND mta.Transaction_Amount = q.Transaction_Amount

Upvotes: 1

bernhof
bernhof

Reputation: 6320

Just group by transaction_date in the outer select as well:

SELECT a.transaction_date, a.transaction_agent_id, max(a.stotal) FROM
(
  SELECT transaction_date,transaction_agent_id, sum(transaction_amount) as 'Stotal' FROM transactions
  GROUP BY transaction_agent_id, transaction_date
) a
GROUP BY a.transaction_agent_id, a.transaction_date

Upvotes: 0

Related Questions