Q

Cumulative quantities

I have a table containing three fields: code, date, quantity. I want to know the date, quantity and cumulative quantity for the given code and period also.

This Content Component encountered an error

I have a table containing three fields: code, date, quantity. I want to know the date, quantity and cumulative quantity for the given code and period also.

What we'll need to do is to add up the quantities on all the rows which have a date that is less than or equal to the date of each row. This means a self-join.

select t1.date
     , t1.quantity
     , sum(t2.quantity) as cumulative_qty
  from yourtable as t1
inner
  join yourtable as t2
    on t2.code = t1.code
   and t2.date <= t1.date
 where t1.code = 'PID'
group
    by t1.date
     , t1.quantity

The self-join will join each row in the table (T1) to all the other rows in the table (T2) for the same code which have an equal or earlier date. Conceptually, for the earliest row, it's joining that row to itself, for the second earliest row, it's joining that row to both itself and the earliest row, for the third earliest row, it's joining that row to both itself and the two earlier rows, etc. By the time you're at the latest row in the table, it's joining that row to itself and all the other rows in the table.

To restrict the results to a specific period, you have to include the period in both the ON clause and the WHERE clause:

select t1.date
     , t1.quantity
     , sum(t2.quantity) as cumulative_qty
  from yourtable as t1
inner
  join yourtable as t2
    on t2.code = t1.code
   and t2.date <= t1.date
   and t2.date between '2006-04-01' and '2006-04-30'
 where t1.code = 'PID'
   and t1.date between '2006-04-01' and '2006-04-30'
group
    by t1.date
     , t1.quantity

Is self-joining each row to a number of other rows like this efficient? It can be, with the proper indexes. But there may be a better way to approach the cumulative sum problem. See Running sums, redux for an approach using a cursor. I don't normally even mention using a cursor, because 99% of the time it's less efficient than a set-based solution, but in this case it deserves consideration.

This was first published in June 2006

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close