Cumulative SQL Totals

Posted on 2011-05-05
Last Modified: 2012-08-13

I have a table with the following data

date                 sales    cost_of _sales   overheads  variance
1/11/10          10000         5000                    1000         50
2/11/10          23000          5000                    2000        150
3/11/10          5000            2000                    900           60
etc. etc.

could someone tell me how to write a select query that would return:-

date               sales        cost_of_sales     overheads  variance
1/11/10         10000          5000                    1000            50
2/11/10          33000        10000                   3000           200
3/11/10          38000        12000                   3900           260

Basically I would like a running cumulative value.

Thanks for the help
Question by:sagarh
    LVL 12

    Accepted Solution

    There may well be a better way than this involving recursion but this is from the top of my head.

    My  main concern would be performance - the execution time of this will grow rapidly with the number of rows in the table.

    Select date,
     , RunningSales = (Select Sum(sales) from MyTable where date<=
     , RunningCost = (Select Sum(cost_of_sales) from MyTable where date<=
     , RunningOverhead = (Select Sum(overheads) from MyTable where date<=
     , RunningVairance= (Select Sum(variance) from MyTable where date<=
       MyTable main
    order by date
    LVL 33

    Expert Comment

    Something else to try:

    with myCTE as (
      select top 1 T.[date], T.sales, T.cost_of_sales, T.overheads, T.variance
      from myTable T
      select T.[date], T.sales + myCTE.sales, T.cost_of_sales + myCTE.cost_of_sales, T.overheads + myCTE.overheads, T.variance + myCTE.variance
      from myTabel T
      join myCTE 
        on myCTE.[date] < T.[date]
    select [date], MAX(sales), MAX(cost_of_sales), MAX(overheads), MAX(variance)
    from myCTE
    group by [date]
    order by 1
    option (maxrecursion 32767)

    Open in new window


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    754 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

    17 Experts available now in Live!

    Get 1:1 Help Now