Trying to update a table with a customer sales averaging column - Oracle SQL Trying to to write a query to add a column to a sales table in order to average sales amounts for individual customers. I have tried quite a bit, ultimately populating the column with a single value over all the rows (not what I need). I have about 75 customers with a total of hundreds of invoices. Coming up blank. Thanks for any advice or help.

ALTER TABLE t1
ADD AVG_AMOUNT NUMBER(9,2);
UPDATE
  SET AVG_AMOUNT = 
    (SELECT CAST(AVG(col1)AS FLOAT)
    FROM t1
    GROUP BY col2;


I tried this but get the same value for the whole column.

ALTER TABLE t1
ADD AVG_AMOUNT NUMBER(9,2);
UPDATE t1
SET AVG_AMOUNT = 
  (SELECT CAST(AVG(INVOICE_TOTAL)AS FLOAT)
FROM t1
INNER JOIN t2
ON t1.customer_ID=t2.customer_ID
WHERE t1.customer_ID = t2.customer_ID
);
In the UPDATE statement you do not calculate the AVG per customer but an AVG on all customers. Put the customer identification in the where clause. The GROUP BY option is not needed since the AVG column is the only column in the SELECT statement (see other reply by @_horse_with_no_name):

UPDATE t1
SET AVG_AMOUNT=(SELECT AVG(INVOICE_TOTAL)
                FROM   t1 a1
                where  t1.customer_ID = a1.customer_ID);