All Forums Database
AJPeyerson 3 posts Joined 07/14
03 Jul 2014
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?
 
Thanks!
AJ

dnoeth 4628 posts Joined 11/04
03 Jul 2014

You need a Windowed Aggregate Function:

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

 

Dieter

AJPeyerson 3 posts Joined 07/14
03 Jul 2014

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!)
 
Thanks!

You must sign in to leave a comment.