howto: simple SQL query for cumulated data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

howto: simple SQL query for cumulated data

Author

Topic

lalbatros
Yak Posting Veteran

69 Posts

Posted - 2008-01-07 : 08:14:02

Hello.

Till now I have been used to display cumulated data in excel files, using pivot tables.
For example, I create SQL queries that summarize stock variations over time, and I display these results cumulated over time in a pivot table using the "running total" option of the pivot tables in excel.

Now I have a need to get cumulated data directly in a VB application (or an access application), and I would like to know if a simple SQL query could produce these cumulated data I could produce so easily in Excel. Of course, like in my xl pivot tables, other keys are involved like: the storage or the product involved.

Thanks for your suggestions

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-07 : 08:33:09

Maybe you can provide the some sample data and the result that you want for the scenario that you mention. Please also provide the table DDL.

KH
[spoiler]Time is always against us[/spoiler]

lalbatros
Yak Posting Veteran

69 Posts

Posted - 2008-01-07 : 08:53:11

I think the details are not needed.
Basically my question is about cumulating data in SQL.
Additional key-fields in the tables have only a secondary role.
Let's assume a record is made of (time,variation).

Let's take this list as a sample table:
{(1,5),(2,-2),(3,6),(4,-1),(5,-2),(6,3)}

The cumulated result would be:
{(1,5),(2,5-2),(3,5-2+6),(4,5-2+6-1),(5,5-2+6-1-2),(6,5-2+6-1-2+3)}
={(1,5),(2,3),(3,9),(4,8),(5,6),(6,9)}

In the real application, there would be additional fields and some aggregation would be needed first.
But the final operation would be calculating the cumulated data.

Thank already

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-07 : 09:18:28

basically what you want can be done with sub-query


select time, 
       (select sum(variation) from sample x where x.time <= s.time)
from   sample s

KH
[spoiler]Time is always against us[/spoiler]

lalbatros
Yak Posting Veteran

69 Posts

Posted - 2008-01-07 : 10:19:36

Exactly what I need, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources