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

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.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close