0

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

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;
|improve this answer|||||
1

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);
|improve this answer|||||
  • col2 is the customer ID, I somehow want to bind the column to the col1 (sales) value in the where clause? How would I do this? – codesquatch Nov 8 '15 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.