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 );