Trying to pull a variable only on row with max(date) while summing a different variable on all rows

I need to pull a value from the row that has the maximum date, while also summing all the values of a different column.

What I mean is something like this:

select

a.account_number,
a.client,
a.referral_date,
sum(b.amount),
max(b.date),
case when b.date = max(b.date) then b.due end as due

from a join b on a.account_number = b.account_number
group by a.account_number, a.client, a.referral_date, sum(b.amount), max(b.date), case when b.date = max(b.date) then b.due end

I'm sorry if this doesn't make sense, but I'm trying to sum ALL of "amount" while only getting "due" from the row with the maximum "date".

So if I join them so it only pulls max(date) I won't be able to sum ALL of the amounts.

I've been searching forever for this, but frankly I don't even know what to type into a search engine for this q开发者_如何转开发uestion. Thank you in advance for your help! Let me know how I can further clarify!

Steven

Wouldn't this work:

select a.account_number
     , a.client,
     , a.referral_date
     , sum(b.amount)
     , case when b.date = max(b.date) then b.due end as due
  from a join b 
    on a.account_number = b.account_number
group by a.account_number, a.client, a.referral_date

... and if you're using PL\SQL then untested but:

select account_number
     , a.client
     , a.referral_date
     , sum(b.amount)
     , max_date
 from ( select a.account_number
         , a.client,
         , a.referral_date
         , b.amount
         , max(b.date) over ( partition by a.account_number, a.client, a.referral_date ) as max_date
      from a join b 
        on a.account_number = b.account_number )
group by a.account_number, a.client, a.referral_date, max_date

Not sure I precisely understand your goal, but how about this:

SELECT account_number, client, referral_date, amount, due
  FROM (SELECT a.account_number,a.client,a.referral_date, b.due, b.date TheDate
             , SUM(b.amount) OVER (PARTITION BY b.account_number) amount
             , MAX(b.date) OVER (PARTITION BY b.account_number) max_dt
          FROM a JOIN b ON a.account_number = b.account_number)
 WHERE TheDate = max_dt;