Jump to content

Recommended Posts

Hi,

 

I wondered if anyone could help me.

 

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

 

post-30849-0-61906100-1430329110_thumb.png

 

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.

Link to comment
https://forums.phpfreaks.com/topic/295953-mysql-sum-query/
Share on other sites

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.

Link to comment
https://forums.phpfreaks.com/topic/295953-mysql-sum-query/#findComment-1510321
Share on other sites

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;
Link to comment
https://forums.phpfreaks.com/topic/295953-mysql-sum-query/#findComment-1510332
Share on other sites

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;
---------------------------
Link to comment
https://forums.phpfreaks.com/topic/295953-mysql-sum-query/#findComment-1510338
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/295953-mysql-sum-query/#findComment-1510433
Share on other sites

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 
Link to comment
https://forums.phpfreaks.com/topic/295953-mysql-sum-query/#findComment-1510438
Share on other sites

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;
Link to comment
https://forums.phpfreaks.com/topic/295953-mysql-sum-query/#findComment-1510452
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.