CODEWITHSUNDEEP
mysqlsqldatabaseforeign-keysmysql-workbench
neilH
neilH

Reputation: 3438

Use UPDATE to populate column of 2nd table with AVG data

Here are my two tables sold_items and categories. :

sold_items:

enter image description here

categories:

enter image description here

sold_items uses id_Categories from the categories table as a foreign key.

I'm using this command to try and populate the average_price of an item based in it's given "idCategories"- so either 1 or 2. I'm using this command to calculate the average price of items with an idCategories classification of 2:

UPDATE categories SET average_price = (SELECT AVG(price) FROM sold_items) WHERE idCategories='2'

This command executes successfully with no errors, but rather than calculating the average price of the two items where idCategories = 2, it averages all of the products prices and inserts the value of 638.00 into the categories table so it looks liker this:

enter image description here

The value of the calculation done correctly would be 999.50 (i.e.1000+999/2). What is wrong with my SQL command?

Thanks in advance

Upvotes: 0

Views: 58

Answers (3)

A. Greensmith
A. Greensmith

Reputation: 375

Try this:

CREATE TABLE sold_items (
    idsold_items INT,
    item_number VARCHAR(7),
    title VARCHAR(14),
    URL VARCHAR(3),
    price INT,
    idCategories INT
)

CREATE TABLE categories (
    idCategories INT,
    model VARCHAR(7),
    averageprice FLOAT(10,2),
    keyword VARCHAR(1)
)

INSERT INTO sold_items
VALUES ('1', '14545', 'Equitek CAD', '...', '99', '1'),
       ('2', '454545', 'Equitek', '...', '454', '1'),
       ('3', '456645', 'Samsung', NULL, '999', '2'),
       ('4', '5456464', 'Samsung Galaxy', '', '1000', '2')

INSERT INTO categories
VALUES ('1', 'Equitek', NULL, ''),
       ('2', 'Samsung', NULL, NULL)

UPDATE categories
SET averageprice = CalculatedAverage.averagePrice
FROM categories
JOIN (
    SELECT
        idCategories,
        [averagePrice] = AVG(price)
    FROM sold_items
    GROUP BY idCategories
) CalculatedAverage ON categories.idCategories = CalculatedAverage.idCategories

Results:

idCategories     model    averageprice    keyword
1                Equitek  276.00
2                Samsung  999.00          NULL

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I would recommend a correlated subquery:

UPDATE categories c
    SET average_price = (SELECT AVG(si.price)
                         FROM sold_items si
                         WHERE si.iDCategories = c.idCategories
                        )
    WHERE idCategories = 2;

This makes it much easier to update multiple categories at once -- or even all of them.

Note: If your ids are numeric (which yours appear to be), don't use single quotes for comparisons.

Upvotes: 1

VDK
VDK

Reputation: 583

You need to specify where idCategories = 2, inside the sub query as well:

UPDATE categories 
SET average_price = (SELECT AVG(price) FROM sold_items WHERE idCategories='2') 
WHERE idCategories=2

Upvotes: 0

Related Questions