Re: Help... update with sum()

sgb <sgb_at_sgb.com> wrote in message news:<tj20lu81qqmm5d2dii68atjliubt2forg8_at_4ax.com>...
> Any assistance with this problem would be really appreciated.
> Supposing I have a table like this: (Three columns: name, amount,
> total. I shall use commas to separate the fields)
>
> NAME, AMOUNT, TOTAL
> joe, 100,
> joe, 500,
> mary, 200,
> mary, 300,
> jane, 100,
>
>
> I would like to update the TOTAL (currently empty) with the sum of the
> amounts for names that are identical. IE:
> select sum(amount) from my_table where name = 'joe'; *** This would
> return 600.
>
> I have been trying to do this using pl/sql but I wonder if it could
> even be done with a simple sql statement? Even the pl/sql required has
> me stumped. Originally I was trying something like this (it obviously
> doesn't work):
>
> cursor total_cur is
> select sum(amount) from my_table where name = name for update;
> loop
> fetch total_cur into v_total;
> update my_table
> set total = v_total
> where current of;
>
>
> -Scott

SUM will always return a single value and you are trying to update a third column in a table with the SUM of selected rows in second column from the same table. This to me looks like a bad design.

I suggest you rethink the design of your table and maybe store totals in another table with one to many to your current table. In this case your current table will hold NAME and AMOUNT and your totals table will hold NAME and TOTAL.

HTH
//Rauf Sarwar Received on Wed Aug 07 2002 - 05:09:44 CEST