Q
Problem solve Get help with specific problems with your technologies, process and projects.

Cumulative sum in Access

This question in MS Access. I am designing a small database and I need to calculate a cumulative sum in a query....

For example if the query has the following data

1 
2
3

then the result should be

1 
3
6

i.e. each cell equal to itself plus the previous one.


Yes, a cumulative sum can be calculated easily, but it requires that the selected rows have some column that you can ORDER BY.

select T1.sortfield
     , T1.datafield
     , ( select sum(datafield) 
           from yourtable as T2
          where T2.sortfield
             <= T1.sortfield ) as cumsum
  from yourtable as T1
order by
       T1.sortfield

For each row, the subselect calculates the sum of all datafield values for all rows where the sortfield is less than or equal to the sortfield of the row in question.

In your example, with only one column in the table, the sortfield is also the datafield, so your query would be

select T1.datafield
     , ( select sum(datafield) 
           from yourtable as T2
          where T2.datafield
             <= T1.datafield ) as cumsum
  from yourtable as T1
order by
       T1.datafield

Although at first it might appear that "the sum of all datafield values for all [previous] rows" and "equal to itself plus the previous one" aren't the same result, they are -- assuming you meant "equal to itself plus the previous total." I'm pretty sure this is what you wanted, because otherwise, if you wanted "equal to itself plus the previous value," then your sample results would have been

1 
3
5

which would be some kind of Fibonacci number and not a cumulative total.

For More Information


This was last published in July 2002

Dig Deeper on Oracle and SQL

PRO+

Content

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

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close