Incremental SUM

Question

Incremental SUM

baranozgul

SSC Veteran

Points: 205
More actions
October 20, 2003 at 6:17 am

#81318

Hi, working on a stored procedure query:
Suppose you have a table as follows:
count | margin
--------------
1 5
4 10
3 15
8 20
And you want to extract the data, with count field representing the sum of counts up to each slice:
count | margin
--------------
1 5
5 10
8 15
16 20
I cannot use #temp tables for other reasons. Anyone may have an idea how to perform the above transformation.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Assuming your column 'margin' is always sorted ascending, this might work



create table #t( f1 int primary key, c INT)

insert #t values(1, 5)

insert #t values(4, 10)

insert #t values(3, 15)

insert #t values(8, 20)

select * from #t

select (select SUM(f1) from #t t1 where t1.c <= t2.c) AS uniq,  t2.c

from #t t2

order by 2

drop table #t

I used a temp table only for 'demonstration purposes'

HTH

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

baranozgul SSC Veteran Points: 205 More actions · Answer 2

Many Thanks,

May I extend the problem a little (or to infinity?)

This time suppose that values

count | margin

--------------

1 5

4 10

3 15

8 20

are not stored in a table, but are selected from an inner query such as:

SELECT (some calculations) as count, (some more calculations) as margin

FROM many joins

WHERE many conditions

An inner query which you would hardly wish to re-run when referencing the resultset as t1 and t2

Sincerely,

Baran

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Why can't you use temp tables?

Also, if you are on SQL2K have you considered the table data type?

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

baranozgul SSC Veteran Points: 205 More actions · Answer 4

I am developing on Crystal Reports, which fails to identify resultset fields when #temp tables are used in storedprocedures.

At the moment I am checking out the usage of the table data type.

Many Thanks.

Sincerely,

Baran

David Burrows SSC Guru Points: 65142 More actions · Answer 5

How about creating a table of possible margin values (counter in my case containing 1 to 100) then doing

select sum([count]) as [count], c.number as margin 

from (select (case when a.margin = b.number then 1 else 0 end) as match, 

a.[count], 

a.margin, 

b.number 

from (your query...) as a 

cross join counter b 

) c 

where c.margin <= c.number 

group by c.number 

having sum(c.match) > 0 

order by c.number

Far away is close at hand in the images of elsewhere.
Anon.

jpipes SSChampion Points: 12230 More actions · Answer 6

quote:
I am developing on Crystal Reports, which fails to identify resultset fields when #temp tables are used in storedprocedures.

Really? What version? We use Crystal for all our reporting needs and almost every single report is generated from temp tables...

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 7

Agrees with jpipes, we also use temp tables.

Do you Crystal Smart Viewer or VB, etc.

lambje SSCrazy Points: 2085 More actions · Answer 8

If you are having problems with temp tables and Crystal Reports you could always create a stored procedure. Crystal should play nice with that.

rstone SSCertifiable Points: 6209 More actions · Answer 9

Is a SET NOCOUNT ON required while processing the temp table?

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Rob Hershfield SSC-Addicted Points: 440 More actions · Answer 10

Why not use a running total in Crystal? The same amount of data will be returned to Crystal either way, and I doubt such a running total would cause a noticable performance degradation in Crystal.