Conditional Sum

Question

Conditional Sum

maffan76

Ten Centuries

Points: 1256
More actions
March 27, 2003 at 2:12 am

#101418

Hi,
i have a value say 1000 and i have atble which contains values whose sum could be 10000. I want to sum the rows till my sum reaches 1000. Is it possible that i can do it in a single query. I can use cursor but i want to avaoid that.
Second question, what are the pros and cons of using CASE statement.
Third whare can i look for the further details for join alogorithms ( Inner LOOP, Merge, Hash) other than BOL.
Kind Regards,
Affan

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 1

I cannot think of any other way of summing the rows until you reach a set number other than processing each row at a time. Maybe someone else does know of a way.

Lots of people say don't use cursors but I use them all over the place for record level processing without any problems. This is record level processing

add row to total

if more than value exit

else fetch next row etc

and a cursor would do the job fine.

With the pros and cons of using CASE - the pros and cons against using what?

Jeremy

maffan76 Ten Centuries Points: 1256 More actions · Answer 2

maffan76

Ten Centuries

Points: 1256

March 27, 2003 at 3:02 am

#452301

General hind sides of using case statements.

dj_meier SSCrazy Points: 2732 More actions · Answer 3

Here are some useful links relating to join types:

http://www.sql-server-performance.com/mb_sql_server_joins.asp

http://www.sql-server-performance.com/hints_join.asp

-Dan

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 4

Maffan76, for some info on joins read this article on sql.nu: http://www.sql.nu/articles/joins.shtml

Regarding your sum question, is this what you mean?

SELECT CASE WHEN SUM(somecol) > 1000 THEN 1000 ELSE SUM(somecol) END FROM sometable

What exactly do you mean with pros and cons of using CASE? There are no general problems using CASE, but of course in some situations there might be better ways of accomplishing something.

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

Edited by - chrhedga on 03/27/2003 08:30:56 AM

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

maffan76 Ten Centuries Points: 1256 More actions · Answer 5

Thanks all for the reply.

Actually i want to distribute a number amongs the first n rows whose sum is equal to the number beign distributed.

e.g. i have 1000 and i want ot distribute this number among the n rows whose sum accumulates to 1000.

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

What order do you want for the selection and does the table have a PK or IDENTITY column?

e.g.

create table #t (IDno int identity(1,1),val int)

insert into #t values(34)

insert into #t values(67)

insert into #t values(17)

insert into #t values(93)

insert into #t values(49)

insert into #t values(55)

insert into #t values(92)

insert into #t values(55)

insert into #t values(22)

insert into #t values(64)

insert into #t values(28)

insert into #t values(55)

declare @limit int

set @limit = 500

select t.IDno,t.val,

isnull((select sum(val) as 'sumval' from #t where IDno < t.IDno),0),

(case when isnull((select sum(val) as 'sumval' from #t where IDno < t.IDno),0)+t.val > @limit then

((isnull((select sum(val) as 'sumval' from #t where IDno < t.IDno),0)+t.val)-@limit)

else t.val end) as 'apportion'

from #t t

where isnull((select sum(val) as 'sumval' from #t where IDno < t.IDno),0) <= @limit

Edited by - davidburrows on 03/28/2003 02:14:23 AM

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

maffan76 Ten Centuries Points: 1256 More actions · Answer 7

maffan76

Ten Centuries

Points: 1256

March 28, 2003 at 3:01 am

#452440

Thanks

I'll look into that

Kind Regards,Affan