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

From: Peter Wu <pwu7_at_ford.com>
Date: 2000/05/30
Message-ID: <8h0vvb$rkt4@eccws12.dearborn.ford.com>#1/1

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