[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Cumulative SQL Totals

Posted on 2011-05-05
2
Medium Priority
?
426 Views
Last Modified: 2012-08-13
Hi

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
0
Comment
Question by:sagarh
2 Comments
 
LVL 12

Accepted Solution

by:
Paul_Harris_Fusion earned 2000 total points
ID: 35698551
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<= main.date)
 , RunningCost = (Select Sum(cost_of_sales) from MyTable where date<= main.date)
 , RunningOverhead = (Select Sum(overheads) from MyTable where date<= main.date)
 , RunningVairance= (Select Sum(variance) from MyTable where date<= main.date)
from
   MyTable main
order by date
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35699162
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
  UNION ALL
  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

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

872 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