Create a running total with SQL select statement

Posted on 2006-05-31
Last Modified: 2006-11-18
I have a set of data in a table which appears as follows

Count    Parent     Child
1           AB20001 TP22226
1           AB20001 TP22258
1           AB20001 TP22252
1           TK55558 TP99562
1           GK88885 CK25996
1           GK88885 CK25996

I would like to create a select statement that will give a result as follows

Count    Parent     Child        Running Total
1           AB20001 TP22226   1
1           AB20001 TP22258   2
1           AB20001 TP22252   3
1           TK55558 TP99562   1
1           GK88885 CK25996   1
1           GK88885 CK25996   2

The addition is the Running total column. This is a running total of the number of instances of the parent code and is being reset on its change. I am unsure of the command that I can use that may be able to achieve this.

Question by:stonehage
    LVL 142

    Accepted Solution

    select count, parent, child, (select count(*) from yourtable i where i.parent = p.parent and i.child <= p.child ) as r_total
    from yourtable p order by p.parent, p.child
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    in sql server 2005, the syntax can be made differently...

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now