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 last published in May 2007

## Content

Find more PRO+ content and other member only offers, here.

#### Have a question for an expert?

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

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

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### Amazon ushers in pay-by-query pricing with Athena data engine

Amazon's Athena data engine brings interactive SQL queries to S3. It's based on an open source framework that Teradata and others...

• ### A look at the EnterpriseDB Postgres data management platform

The EDB Postgres platform, which is based on the PostgreSQL open source relational database, is offered as a subscription service...

• ### Louisiana uses GIS software to create map to flood preparedness

The Louisiana Department of Health responded to flooding with the help of GIS software that located trouble spots with at-risk ...

• ### Business should work with IT to get self-service BI governance right

Self-service analytics is all about getting IT roadblocks out of the way of the business, but traditional tech teams can still ...

• ### Creative projects leave people guessing about future impact of AI

A push is underway to write creative AI algorithms that can engage in music, film and design projects. So far, they have ...

• ### Data visualization plays an important role in a big data environment

Enterprises are increasingly putting data visualization tools, like Tableau, at the top of their big data platforms to start ...

## SearchSAP

• ### Does SAP ONE Support Launchpad make SAP support any easier to use?

The new Fiori user experience makes it easier to access applications and support services, but product-specific support still ...

• ### At SME Summit, SAP Anywhere growing pains revealed

Attendees at an SAP SME Summit lauded the e-commerce front end, but some said back-end integration and payment-processing ...

• ### Planning a HANA big data strategy with SAP HANA Vora

SAP has worked hard to position HANA as a big data platform. To formulate a viable big data strategy, you need to know the tools,...

## SearchSQLServer

• ### How to get the most out of virtual SQL Server with Microsoft Hyper-V

SQL Server is a CPU-intensive technology, which can make it tricky to run in a virtualized environment. Keep your SQL Server ...

• ### Microsoft previews SQL Server on Linux, opens features across editions

Microsoft looks to broaden the horizons of SQL Server, as it moves some Enterprise features to Standard Edition and issues the ...

• ### Cask framework aims to speed Azure HDInsight data pipeline builds

A link between Cask Data's CDAP application and integration environment and Azure HDInsight, Microsoft's Hadoop cloud service, is...

## TheServerSide

• ### DOES16 keynotes quantify the benefits of a DevOps transition

At the DevOps Enterprise Summit 2016, Tripwire founder Gene Kim stated his case on why every organization should embark upon a ...

• ### How Target improved software delivery by adopting DevOps processes

At the 2016 DevOps Enterprise Summit, Target's Heather Mickman describes the many ways the adoption of DevOps processes improved ...

• ### How Agile transformations are sparked by successful DevOps adoption

Tasktop's Mik Kersten discusses how successful DevOps adoption projects often lead to large Agile transformations.

## SearchDataCenter

• ### Compare benefits of a blade server architecture vs. hyper-convergence

To determine if a series of blade servers is the right call for more condensed compute in your data center, evaluate its benefits...

• ### A hyper-converged data center offers SDDC opportunity, IT scalability

Hyper-convergence can increase scalability and pave the way toward software-defined infrastructure. Use these five tips to learn ...

• ### OpenStack enterprise adoption still awaits full embrace

OpenStack in the enterprise is more likely to see continued adoption via vendor distributions and managed services, not the raw ...

## SearchContentManagement

• ### Five steps for designing an effective BI dashboard

The BI dashboard can be a very useful tool for data engagement if the dashboard is designed and used effectively. Here are five ...

• ### Examining the top offerings in the WCM platform marketplace

Once you've decided that a WCM platform is right for your business, it's time to decide which to choose. Expert Geoffrey Bock ...

• ### Why Microsoft Teams could change up the collaboration software market

Microsoft's new collaboration tool, Teams, could give Slack a run for its money. Expert Reda Chouffani offers a look at five ...

## SearchFinancialApplications

• ### No one-size-fits-all strategy for cloud ERP software migration

Experts say a cloud ERP transition plan will vary according to a variety of factors, from company size to an organization's ...

• ### HR video apps spreading beyond training to recruiting, engagement

Users say cost savings, faster turnaround and better corporate branding are some of the benefits from using video at more stages ...

• ### Microsoft picks SAP for core HCM tools and global talent management

Microsoft chooses SAP SuccessFactors for core HR and talent management, and SAP selects Microsoft Azure as a preferred public ...

Close