CODEWITHSUNDEEP
mysql
Psytanium
Psytanium

Reputation: 39

Sum values from the same column where ids are similar

I'm trying to share the quantity of products of the same group (if not zero), using a query.

It's a website using PHP5.6 and MySQL queries.

Database table Products

+-------------+-------------------+-------------------+
| products_id | products_quantity |  products_master  |
+-------------+-------------------+-------------------+
|      11     |         1         |       12          |   
|      12     |         5         |       12          |
|      13     |         2         |       12          |
|      14     |         7         |       0           |
|      15     |         9         |       0           |
+-------------+-------------------+-------------------+

Expected results

+-------------+-------------------+-------------------+
| products_id | products_quantity |  products_master  |
+-------------+-------------------+-------------------+
|      11     |         8         |       12          |   
|      12     |         8         |       12          |
|      13     |         8         |       12          |
|      14     |         7         |       0           |
|      15     |         9         |       0           |
+-------------+-------------------+-------------------+

I need help write this code 

$sql = "SELECT products_id, IF(products_master != 0, (missing code to sum quantity), products_quantity) AS products_quantity FROM products ORDER BY products_quantity";

Products with the same products_master (if not zero) have the sum of quantity.

Upvotes: 1

Views: 40

Answers (1)

Tree Frog
Tree Frog

Reputation: 666

You can use a join and group in the sub query

select p.products_id, 
    case products_master when 0 then products_quantity else x.qty end
from products p
left join  (select products_master, sum(products_quantity) as qty 
       from Products group by products_master) x 
on p.products_master = x.products_master

Upvotes: 1

Related Questions