MYSQL Sum Query

[MoFish]

Hi,

 

I wondered if anyone could help me.

 

Please see the attached screenshot as an example of my DB.

 

 

I'm looking to write a SQL query which adds a 'total' value into the product table.

 

This will do a SUM on product_a.price and product_b.price WHERE product.product_id equals a specific id.

 

I have tried to get this working, but am struggling.

 

Any help much appriciated.

 

Thanks, MoFish.

[Barand]

If I were you I wouldn't store the product total at all. Just calculate it from a query when required. Don't store derived data in a database.

 

Also, why two tables, a and b, with identical structures. Have a single table with an extra column to identify a and b.

 

DECIMAL type is better option than FLOAT for prices.

 

If you do store the product total, you will need a column for it which you don't currently have according to your model.

[Barand]

But, should you want to update regardless, assuming you add a "total" column to the product table

UPDATE product p
INNER JOIN
    (
    SELECT product_id, SUM(price) as tot
    FROM
        (
        SELECT product_id, price FROM product_a
        UNION ALL
        SELECT product_id, price FROM product_b
        ) ab
    GROUP BY product_id
    ) tots ON p.id = tots.product_id
SET p.total = p.total + tots.tot;

If you combine the a and b tables as suggested then

UPDATE product p
LEFT JOIN
    (
    SELECT product_id, SUM(price) as tot
    FROM product_
    GROUP BY product_id
    ) tots ON p.id = tots.product_id
SET p.total = p.total + tots.tot;

[gizmola]

Kuddos for attaching an ERD.

 

The product table doesn't have a 'total' column in it.

 

Just out of curiosity, why do you have product_a and product_b tables?

 

Well that's what i get for writing the post and then going away for 2 hours instead of posting it --- Barand of course provides a full answer for you.

 

In case you want to dig into Barand's answer some more, you can break it down into smaller pieces. Obviously the important part of this is the union, but your structure doesn't seem warranted in the first place, and wouldn't be needed if you didn't have product_a and product_b tables.

 

 

---------------------------

Here's a query to get you started on solving your problem that might help you:

 

select u.product_id, SUM(u.price) FROM (select product_id, price from product_a UNION ALL select product_id, price from product_b) u GROUP BY u.product_id;

---------------------------

[MoFish]

Hello,

 

Thank you for your replies.

 

I'm not looking up update the product table with a value of total, ideally this would just be an alias on the select query.

 

You are both correct in that table_a and table_b are identical, but this is for demonstrations sake to paint the picture. The tables are different and have differing fields.

 

I'm assuming following on from your above queries, if I do something like the below with the appropriate joins, I would then be able to display the 'total' on my page without having the field in the table?

SELECT product_id, SUM(price) as total     

Please correct me if I am mistaken or have misunderstood

 

Thanks again guys,

 

MoFish

[Barand]

I'm assuming following on from your above queries, if I do something like the below with the appropriate joins, I would then be able to display the 'total' on my page without having the field in the table?

SELECT product_id, SUM(price) as total     

Please correct me if I am mistaken or have misunderstood

 

Yes. If $result is the result from your query execution, then something like

$row = $result->fetch_assoc();
echo $row['total']; // print the total 

[MoFish]

Thanks for your response Barand.

 

So adapting your query above to not include the update statement, what should be query look like?

 

Thanks,

 

MoFish

[gizmola]

Thanks for your response Barand.

 

So adapting your query above to not include the update statement, what should be query look like?

 

Thanks,

 

MoFish

Did you look at my answer?

[Barand]

As Gizmola said in his reply

UPDATE product p
INNER JOIN
    (
    SELECT product_id, SUM(price) as tot            -- you
    FROM                                            -- would
        (                                           -- need
        SELECT product_id, price FROM product_a     -- these
        UNION ALL                                   -- eight
        SELECT product_id, price FROM product_b     -- lines
        ) ab                                        -- of the
    GROUP BY product_id                             -- query
    ) tots ON p.id = tots.product_id
SET p.total = p.total + tots.tot;

[MoFish]

Hello,

 

I do appologise gizmola, I overlooked your reply on my phone. You seem to have hit the nail on the head, and this query is now calculating as expected.

 

Thank you for both replying and again, appologise for the confusion.

 

Regards,

 

MoFish