By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,641 Members | 2,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,641 IT Pros & Developers. It's quick & easy.

SQL: Running Sum?

P: n/a
Can anybody point me to an example of creating a running sum via SQL.

The situation is the application shows a history of bond trades - both as parent
transaction and as allocated among funds. The user wants to see the current
total holdings after each trade - both at the parent and fund allocation level.

I wimped out and created a "CurrentNetBalance" field in each of the two tables
and maintained it when processing a trade - having jumped to the conclusion
that recalculating same for every row shown would kill performance.

The obvious has been pointed out to me: that that's a poor solution, laden with
additional maintenance issues when the user wants to delete or retroactively
modify a trade.

I'm pretty sure something like that can be done via SQL on a small dataset
without too much of a performance hit because the SQL below runs quite quickly.
I'd try to adapt that to my needs, but I don't have a clue how or why it
works... and I've also found that it breaks sometimes.

-------------------------------------------------
SELECT (SELECT Count(*) FROM tlkpCountry as xxx
WHERE xxx.Country < tlkpCountry.Country)+1 AS RowNum, tlkpCountry.Country
FROM tlkpCountry;
-------------------------------------------------

So, bottom line, can somebody aim me at an example?
--
PeteCresswell
Oct 26 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
"(PeteCresswell)" <x@y.Invalidwrote in
news:0h********************************@4ax.com:
Can anybody point me to an example of creating a running sum via SQL.

The situation is the application shows a history of bond trades - both
as parent transaction and as allocated among funds. The user wants
to see the current total holdings after each trade - both at the
parent and fund allocation level.

I wimped out and created a "CurrentNetBalance" field in each of the
two tables and maintained it when processing a trade - having jumped
to the conclusion that recalculating same for every row shown would
kill performance.

The obvious has been pointed out to me: that that's a poor solution,
laden with additional maintenance issues when the user wants to delete
or retroactively modify a trade.

I'm pretty sure something like that can be done via SQL on a small
dataset without too much of a performance hit because the SQL below
runs quite quickly. I'd try to adapt that to my needs, but I don't
have a clue how or why it works... and I've also found that it breaks
sometimes.

-------------------------------------------------
SELECT (SELECT Count(*) FROM tlkpCountry as xxx
WHERE xxx.Country < tlkpCountry.Country)+1 AS RowNum,
tlkpCountry.Country FROM tlkpCountry;
-------------------------------------------------

So, bottom line, can somebody aim me at an example?
Not me ...

We'va had a very long running thread here recently about something
similar; someone wants to update each row, or display for each row, data
calculated on the basis of other rows, usually preceding rows, however
preceding may be defined.

IMO SQL was not designed with this capability in mind. What are the
solutions?

1. Abuse the SQL so that it is recursive and calculates for each row the
required summmative data for all preceding rows as per your example; as
the number of rows increases this gets slower and slower as it requires
recalculation on something like n(n+1)/2 rows;
2. Open a recordset and scan through it, updating a summative field or
fields; this is as current as loading the dataset, which is often not
current enough, especially if the scan is not instantaneous; but a scan
is a scan, it's not a repetitive scan, that is row 1 is or should be
looked at once and once only; some people think that disconnecting a
recordset makes this solution more digestible;
3. Display the data in a report and use the report's running sum
capbilities;
4. If we are using MS-SQL or similar powerful engine we can hide all of 1
above in a Stored Procedure or UDF as well as do it more efficiently; all
the work will be done on the server; many say this is a good thing; I am
not so sure; while your system is idle do you want the server's system to
be doing the work of 100 users like you?
5. I don't know if cross tabs are a solution to this;
6. Combine the notion of opening a recordset with the idea of display;
this is done regularly in html display of data and can be done in Access
by setting the form's (or in Access 2003 ADPs report's) recordset to the
opened recordset(ADO). Of course, we can display html in Access if we are
willing to use the WebBrowser Control.

Are there other solutions? I would be very happy to be reminded of them
or to be instructed in them.

IMO the report is the preferred solution in most cases. Reports have very
powerful grouping and summative capabilities. And the client sees but
cannot mess with the results.

--
Lyle Fairfield
Oct 26 '06 #2

P: n/a
Per Lyle Fairfield:
>1. Abuse the SQL so that it is recursive and calculates for each row the
required summmative data for all preceding rows as per your example; as
the number of rows increases this gets slower and slower as it requires
recalculation on something like n(n+1)/2 rows;
Thanks for taking the time.

That was my concern - and the reason I posted instead of just going with what
Google came up with... as in
http://www.databasejournal.com/featu...le.php/3112381
--
PeteCresswell
Oct 26 '06 #3

P: n/a
Per Lyle Fairfield:
>Are there other solutions? I would be very happy to be reminded of them
or to be instructed in them.
The one I came up with (denormalizing the totals and maintaining them) isn't
looking as bad now as it did last nite.... -)
--
PeteCresswell
Oct 26 '06 #4

P: n/a

(PeteCresswell) wrote:
Per Lyle Fairfield:
Are there other solutions? I would be very happy to be reminded of them
or to be instructed in them.

The one I came up with (denormalizing the totals and maintaining them) isn't
looking as bad now as it did last nite.... -)
--
PeteCresswell
Examples here.

http://search.support.microsoft.com/...7&mode=a&adv=1

Oct 26 '06 #5

P: n/a
Lyle Fairfield wrote:
"(PeteCresswell)" <x@y.Invalidwrote in
news:0h********************************@4ax.com:
Can anybody point me to an example of creating a running sum via SQL.
4. If we are using MS-SQL or similar powerful engine we can hide all of 1
above in a Stored Procedure or UDF as well as do it more efficiently; all
the work will be done on the server; many say this is a good thing; I am
not so sure; while your system is idle do you want the server's system to
be doing the work of 100 users like you?
Lyle Fairfield
You've stated what I've only thought in the past. Normally it's a
no-brainer that distributing the processing out to the "client side"
has many advantages over loading up the server. There are a couple of
other things of which you need to be aware. Both Microsoft and Intel
are saying that because of the problem of cooling CPU's, the trend is
about to turn sharply in the direction of more and more cores (think 20
to 40 sometime soon). So is it better to have all that wasted latency
on the server or on the client machine? Server software is going to be
the first to take full advantage of the parallel capabilities of the
multi-core architecture, but later the client machine software will
also be able to take proper advantage of its own multi-core
capabilities. I don't disagree with your point, I'm simply saying that
the decision is not as much of a no-brainer as it seems to be on the
surface.

BTW, I came up with a couple of different ways of getting a quickly
executing running sum in a query using functions (one used a static
variable). This technique worked properly except for the reevaluation
when the query result window was changed so I dumped that idea. As you
say, the fact that reports don't have that limitation makes them a
candidate for running sums. One interesting result is that a UDF
inside a query is called once prior to it's first use within the query.

James A. Fortune
CD********@FortuneJames.com

Oct 30 '06 #6

P: n/a

"(PeteCresswell)" <x@y.Invalidschreef in bericht news:0h********************************@4ax.com...

I'm pretty sure something like that can be done via SQL on a small dataset
without too much of a performance hit because the SQL below runs quite quickly.
I'd try to adapt that to my needs, but I don't have a clue how or why it
works... and I've also found that it breaks sometimes.

-------------------------------------------------
SELECT (SELECT Count(*) FROM tlkpCountry as xxx
WHERE xxx.Country < tlkpCountry.Country)+1 AS RowNum, tlkpCountry.Country
FROM tlkpCountry;
-------------------------------------------------

So, bottom line, can somebody aim me at an example?
--
PeteCresswell
You might want to take a look at this:
http://www.fabalou.com/access/Querie...TotalQuery.asp

and this
http://www.fabalou.com/Access/Queries/querycreator.asp
This add-in is available since the Access 2.0 days if I recall correctly.

Arno R
Oct 30 '06 #7

P: n/a
CD********@FortuneJames.com wrote:
Both Microsoft and Intel
are saying that because of the problem of cooling CPU's, the trend is
about to turn sharply in the direction of more and more cores (think 20
to 40 sometime soon).
See:

http://www.gotw.ca/publications/concurrency-ddj.htm

James A. Fortune
CD********@FortuneJames.com

Dec 8 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.