SUM of rows with max date

Question

Hope you can help me with this one. Supose I have a table which looks like this (sorry, but my reputation isn't high enough to post images):

[ACCOUNT] [PROGRAM] [DATE] [AMOUNT]

[500] [P1] [10/10/2014] [4.2]
[500] [P2] [09/08/2010] [5.1]
[501] [P1] [08/02/2010] [2.3]
[501] [P3] [10/11/2014] [9.2]

Now, grouping by account, I want to sum the amounts, while keeping the program belonging to the latest date. I want to achieve this, in Oracle SQL:

[ACCOUNT] [PROGRAM] [AMOUNT]

[500] [P1] [9.3] 
[501] [P3] [11.5]

Could anybody lend me a hand, please? Thank you very much!

@Marc B, I think he wants the PROGRAM related to the MAX(DATE). — Wagner DosAnjos, Aug 29 '14 at 16:20

Bulat · Answer 1 · 2014-08-29T16:28:43.660

Try this:

SELECT a.account, b.program, a.amount FROM
(SELECT account, SUM(amount) as amount from table) a INNER JOIN
(SELECT account, program FROM table t 
 WHERE NOT EXISTS (
     SELECT * FROM table 
     WHERE account = t.account and date > t.date)) b 
  ON a.account = b.account

or this might also work:

SELECT t.account, t.program, t.amount 
FROM
(SELECT account, program, SUM(AMOUNT) OVER (PARTITION BY account, program), date  
 FROM table) t 
 WHERE NOT EXISTS 
   (SELECT* from table 
    WHERE account = t.account and date > t.date) b

score 0 · Answer 2 · edited May 23 '17 at 12:12

This is a very common problem that no one has a great answer for: "Show me other columns on the rows matching my min()/max() aggregate criteria." If this is a big table and performance counts, nothing can touch the approach I outlined here and here. Try:

select account
     , program = substr(val, 9)
     , date = to_date(substr(val, 1, 8))
     , amount
from (  select account
             , amount = sum(amount)
             , val = max(to_char(date, 'YYYYMMDD') + program)  --latest date plus other data you want glued together in a sortable string
        from yourtable 
        group by account) t

My apologies if my Oracle syntax is off - I'm a T-SQL guy.

SUM of rows with max date

2 Answers2