Re: How to update the summary in the same table using simple SQL.

I'm not going to comment on the table design... (Ever heard of normalization? You will soon run into problems with tables like this. Actually you already do, as you are going to store the sum multiple times)
Apart from that

 UPDATE pwu7_tmp o SET
   sum =
/*!!!!!*/

      SELECT SUM( rate )
      FROM pwu7_tmp n
/*!!!!!*/      WHERE o.cat = n.cat
      GROUP BY cat
      )

Hth,

Sybrand Bakker, Oracle DBA

Peter Wu <pwu7_at_ford.com> schreef in berichtnieuws 8h0vvb$rkt4_at_eccws12.dearborn.ford.com...
> I have many items belonging to different catagories, each of the items has
> individual rate and I want to get the total of the rates belonging to the
> same catagory and update it in the table.
>
> Can I do it in simple SQL?
>
> Thanks
>
> Peter
>
> *********************************************************************
> Here is the table:
>
> CREATE TABLE pwu7_tmp
>
> id NUMBER,
> cat NUMBER,
> rate NUMBER,
> sum NUMBER
> );
>
> Sample data:
>
> INSERT INTO pwu7_tmp VALUES( 1, 1, 1, 0 );
> INSERT INTO pwu7_tmp VALUES( 2, 1, 2, 0 );
> INSERT INTO pwu7_tmp VALUES( 3, 1, 3, 0 );
> INSERT INTO pwu7_tmp VALUES( 4, 2, 4, 0 );
> INSERT INTO pwu7_tmp VALUES( 5, 2, 5, 0 );
> INSERT INTO pwu7_tmp VALUES( 6, 2, 6, 0 );
> INSERT INTO pwu7_tmp VALUES( 7, 3, 7, 0 );
> INSERT INTO pwu7_tmp VALUES( 8, 3, 8, 0 );
> INSERT INTO pwu7_tmp VALUES( 9, 4, 9, 0 );
> INSERT INTO pwu7_tmp VALUES( 10, 5, 1, 0 );
> INSERT INTO pwu7_tmp VALUES( 11, 5, 2, 0 );
> INSERT INTO pwu7_tmp VALUES( 12, 5, 3, 0 );
> INSERT INTO pwu7_tmp VALUES( 13, 5, 4, 0 );
> INSERT INTO pwu7_tmp VALUES( 14, 5, 5, 0 );
> INSERT INTO pwu7_tmp VALUES( 15, 6, 6, 0 );
> INSERT INTO pwu7_tmp VALUES( 16, 6, 7, 0 );
> INSERT INTO pwu7_tmp VALUES( 17, 6, 8, 0 );
> INSERT INTO pwu7_tmp VALUES( 18, 6, 9, 0 );
> INSERT INTO pwu7_tmp VALUES( 19, 8, 10, 0 );
> INSERT INTO pwu7_tmp VALUES( 20, 8, 12, 0 );
>
> SVRMGR> select * from pwu7_tmp;
> ID CAT RATE SUM
> ---------- ---------- ---------- ----------
> 1 1 1 0
> 2 1 2 0
> 3 1 3 0
> 4 2 4 0
> 5 2 5 0
> 6 2 6 0
> 7 3 7 0
> 8 3 8 0
> 9 4 9 0
> 10 5 1 0
> 11 5 2 0
> 12 5 3 0
> 13 5 4 0
> 14 5 5 0
> 15 6 6 0
> 16 6 7 0
> 17 6 8 0
> 18 6 9 0
> 19 8 10 0
> 20 8 12 0
> 20 rows selected.
>
> Here is the summary for indivual catagory:
>
> SELECT cat, SUM( rate )
> FROM pwu7_tmp
> GROUP BY cat;
>
> CAT SUM(RATE)
> ---------- ----------
> 1 6
> 2 15
> 3 15
> 4 9
> 5 15
> 6 30
> 8 22
> 7 rows selected.
>
> Now, I am going to update the sum column but get back error message.
>
> UPDATE pwu7_tmp o SET
> sum =
>
> SELECT SUM( rate )
> FROM pwu7_tmp n
> GROUP BY cat
> )
> WHERE o.cat = n.cat;
>
>
> ORA-00904: invalid column name
>
>
>
>
>
>
>
>
>
>
>
>
>
Received on Tue May 30 2000 - 00:00:00 CDT