views:

41

answers:

2

Suppose I have a table amountInfo with columns (id, amount1, amount2, amount3) where amountX are money values and id is an int value ranging from 1 to some int under 10.

Currently I have some code that approximately does this:

declare @id int, @idmax int, @amounttotal money
select @idmax = (select max(Id) from amountInfo)
while (@id <= @idmax)
begin
    select @amounttotal = sum(amount1 + amount2 + amount3) 
                          from amountinfo where id=@id
    -- do something with @amounttotal
    select @id=@id+1
end

Is there a way to factor out the while loop with some kind of complex select statement here? I am experienced with C/C++/C# programming but SQL is kinda new to me. Thanks!

EDIT: basically the "--do something" part involves inserting the individual @amounttotals into another table. The actual query I'm running is more complicated than that but I'm hoping to solve this simple problem first before posting a huge example of code.

+1  A: 

You don't need the WHILE loop, use:

SELECT @amounttotal = SUM(t.amount1 + t.amount2 + t.amount3)
  FROM AMOUNTINFO t
 WHERE t.id BETWEEN @id AND @idmax

...but you need to describe what you're doing in the WHILE loop to the @amounttotal variable for us to help you further.

OMG Ponies
but how does this address `-- do something with @amounttotal`?? The "do something" could be just about anything: call a stored procedure, UPDATE tableX, etc.??? This answer is clearly not what the original loop is doing. BTW, nice new picture, you might need taller boots though ;-)
KM
@KM, Right I need the individual totals from each row, getting the entire sum is not what I want
Rich
+5  A: 

try something like this:

INSERT INTO OtherTable
        (id, col1, col2, ...)
    SELECT 
        dt.id, t.col1, dt.TotalSum
        FROM (SELECT
                  id, SUM(t.amount1 + t.amount2 + t.amount3) AS TotalSum
                  FROM AMOUNTINFO
                  GROUP BY id
                  WHERE id>=@id AND id<=@idmax
             ) dt
            INNER JOIN  AMOUNTINFO t ON dt.id=t.id
KM
This looks good, but shouldn't the comparison in the where clause be "id>=@id AND id < @idmax"?
Garett
@Garett: The original while loop had <= so I think that's right in the where clause. Being picky, I'd take the ',...' out of the answer as it's not matching the select. Otherwise, still giving it +1, looks like the right idea.
Jim Leonardo
@Jim. Yes you're right. I noticed the instructions said "ranging from 1 to some int under 10", but I didn't pay attention to the original loop.
Garett
Thanks KM, this is the beginnings of what I need to solve my real problems: the nested selects and group by clause are the key.
Rich