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 );
Your second statement goes into the right direction, but you have a join too many in there, you don’t need to join the table again. You just need a co-related sub-query (one that references the outer table without a join):
UPDATE t1 SET AVG_AMOUNT = (SELECT AVG(INVOICE_TOTAL) FROM t1 t2 -- note a different alias where t1.customer_ID = t2.customer_ID);
Another option is to use the MERGE statement which might be faster:
merge into t1 using ( select customer_id, avg(invoice_total) as avg_invoice from t1 group by customer_id ) x ON (x.customer_id = t.customer_Id) when matched then update set t1.avg_amount = x.avg_invoice;
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);