SELECT....SUM... problem, please help

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

Transact-SQL (2000)

SELECT....SUM... problem, please help

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2007-11-19 : 09:23:26

Hi

(Hope I can make myself clear here)

I have a table (A) in which I hold a list of figures (Amount1) that I want to total up so that the SUM is less than or equal to 2355000. (I want to get as close to this figure as possible)

I already know that if i just SUM up the figures in the column it will be above the figure 2355000, (which is no good to me as i have to keep as close to this figure as possible).
I have to include ALL the amounts in the column, but I can replace/change any figures within that column to make sure that all figures added up (the SUM) come as near as possible to the figure above (2355000).

So the rules are:-
1. Get the SUM of all figures in the column Amount1.
2. If the total is above 2355000 then replace some of the figures to reduce the SUM.

So, in effect what I have to do is within a loop, get the SUM, if it is > 2355000 then loop through again, but this time replace any figures that are higher than a pre-chosen figure with this pre-chosen figure.
Keep looping through, reducing the pre-chosen figure until the SUM is equal to 2355000 (or just below 2355000 will do).

What I have tried (does not work though) is something like this:-

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

declare @roof int
set @roof = 85000

while (SELECT SUM(amount1) AS tot_funds FROM A) < 2355000
BEGIN
IF (SELECT amount1 FROM A) > @roof
BEGIN
-- replace the figure in Amount1 with the figure @roof
set tot_funds = tot_funds + @roof
END
set @roof = @roof - 10
END
----------------------------------------------------------------

I guess the above SQL is totally way off but any help would be appreciated as I'm trying to do this in SQL Server rather than in VB.

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-19 : 09:31:54

Well here's my untested atempt :D
[CODE]
DECLARE @sum int
SET @sum = (SELECT Sum(amount1) FROM A)

WHILE @sum > 2355000 BEGIN
UPDATE A
SET amount1 = amount1 - 1
WHERE id =
(SELECT TOP 1 FROM A WHERE amount1 =
(SELECT MAx(amount1) FROM A))
END

SELECT Sum(amount1) FROM A
[/CODE]

George
<3Engaged!

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-11-19 : 09:46:40

set ROWCOUNT 1
declare @A table (amount1 int)
declare @roof int
set @roof = 85000

insert into @A values (1350000)
insert into @A values (1050001)
insert into @A values (7000)

set nocount on

while (SELECT SUM(amount1) AS tot_funds FROM @A) > 2355000
BEGIN
update @A set amount1 = amount1-1 WHERE amount1 = (select max(amount1) from @A)
END

select sum(amount1) from @A

set ROWCOUNT 0

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-19 : 10:42:39

What if there are 10 records with the same amount, which happens to be the max?
This will reduce the total count by 10, instead of 1!

George
<3Engaged!

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-11-19 : 10:57:00

No that is why I used set ROWCOUNT 1
Only one row will be updated each loop

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-19 : 11:55:15

Aha, I missed that part, good catch :D

George
<3Engaged!

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2007-11-20 : 00:25:40

Thanks both, I will look at this again today.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources