Q

The running total self-join

How to find the cumulative sum without using an analytical function?

How to find the cumulative sum without using an analytical function? For example,

1     150      150
1     190      340
2     140      480
3      80      560
3      10      570
8      90      660
6     190      850

There is a technique in SQL which uses a self-join to match each row to the next row. With this type of query, you can produce data such as rank or position, row differences, running totals, and so on.

Within this self-join, it is necessary to specify a join condition. You cannot just join each row to all other rows (which would be a cross join). Specifically, you want each row to join to the next row.

This is easy if you know which row is the next row. Clearly, this can be done only based on the values in one or more columns, since, as we all know, there is no such thing as position of rows within a relational database table. Rows are stored anywhere. Sequencing can be achieved only using the ORDER BY clause in SELECTs. Sequencing is also implicit in the inequality comparison operators.

For example, consider a self-join based on one row having a greater value in the join column than the other:

select ...
  from daTable as r1
left outer
  join daTable as r2
    on r2.foo > r1.foo

Here, each row (r1) is joined to all rows (r2) which have a higher value in the foo column. This is a perfectly valid self-join.

This is not yet the complete solution, though, because what we really want to do is join each row to only one other row that has a higher value, namely the lowest of these, i.e. only the next row. It should be no surprise that this will be done with a subquery that uses MIN().

However, this is a good time to stop working on the solution and look back at the actual question in this example. If the middle column is the data being accumulated, and the right column is the running total, this leaves the left column as the only possible column that we can use for the self-join's ON clause.

And sadly, the left column isn't in sequence. The sample data is deficient.

What? Is that it? Is this the end of the story of the running total self-join? No. Please stay tuned. There is more to come, in a future answer.

This was first published in May 2007

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close