Sum of totals with last location:


Sum of totals with last location:

I have a table that has a general structure of the following:

customer     charge_type     charge_amt     location     datetime

abcd            001                  100.00             A123           7/1/2014 00:00:00

abcd            002                   50.00              B123           7/2/2014 00:00:00

I want to end up with the following data:

customer     charge_amt     location

abcd            150.00             B123

That is, I want the customer, the total charge_amt, and the final location based on the datetime. I'm currently doing it in two steps:

customer and charge_amt

customer and location

and then joining them.

Is there a better way? Faster way? I'm working with ~1,000,000 records, so not a HUGE dataset, but there are other steps involved in this.

So, any advice?



Junior Contributor

Re: Sum of totals with last location:

You need a Windowed Aggregate Function:

sum(charge_amt) -- total amout
over (partition by customer),
from tab
qualify row_number() -- latest row
over (partition by customer
order by datetime desc) = 1

Re: Sum of totals with last location:

Thanks! I'd used the qualify for the location table, but didn't know I could do the sum() over(). That helped a lot (both with this and in the future!)