add average column into same table

Question

I have a table like: Table1 And I need to add a column to that table as average delivery time avr_del_time column added My code is:

SELECT a.vendor, a.part_nr, a.delivery_time, b.avr_del_time
FROM  Table1 a
INNER JOIN (SELECT AVG(delivery_time) AS avr_del_time FROM Table1 GROUP BY vendor, part_nr) b
ON a.vendor = b.vendor, a.part_nr=b.part_nr

please guide me...

Answer 1

Your query is on the right track. You just need to fix up the SQL mistakes:

SELECT a.vendor, a.part_nr, a.delivery_time, b.avr_del_time
FROM Table1 as a INNER JOIN
      (SELECT  vendor, part_nr, AVG(delivery_time) AS avr_del_time
       FROM Table1
       GROUP BY vendor, part_nr
     ) as b
     ON a.vendor = b.vendor AND a.part_nr = b.part_nr;

Notes:

• In the GROUP BY subquery, you need the keys in the SELECT.
• You have a comma in the ON condition; it should be AND.
• I would suggest that you use table aliases that are abbreviations for the table names.

Answer 2

Don't see any issue with your posted code except the JOIN ON condition which should

ON a.vendor = b.vendor
AND a.part_nr=b.part_nr // OR condition can as well be based on requirement

Also since it's a computed value, no point in adding a separate column to your table rather have that column in your display result while fetching from table

Answer 3

You need to return the joined fields in the subquery or the join won't work

SELECT a.vendor, a.part_nr, a.delivery_time, b.avr_del_time
FROM  Table1 a
INNER JOIN (SELECT Vendor, 
                   part_nr, 
                   AVG(delivery_time) AS avr_del_time 
            FROM Table1 
            GROUP BY vendor, part_nr) b
ON a.vendor = b.vendor and a.part_nr=b.part_nr