Sum values from the same column where ids are similar

[Anonymous]

'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.

Someone provided a solution but the query is not returning any rows :

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

 

[Anonymous]

If you want to select only products where product_master is not zero

You could use this as a sub query

SELECT product FROM table WHERE product_master != 0

Is it possible for product_master to be negative? what happens then?

NOT zero can include -1 for instance

 

[Anonymous]

If you want to select only products where product_master is not zero

You could use this as a sub query

SELECT product FROM table WHERE product_master != 0

Is it possible for product_master to be negative? what happens then?

NOT zero can include -1 for instance

No it cannot be negative, zero or positive, how the code will look like now ?

 

[Anonymous]

No it cannot be negative, zero or positive

Just trying to make you think, computers don't know what you intend to do, they do what you tell them to do... regardless.

A number can be negative, therefore -1 will not be zero:

<?php

function my_func(){
  return 0;
}

$value = 18;

$a = [5,
      0.1,
      0,
      null,
      true,
      false,
      10,
      100,
      1000000000,
      -0.1,
      -1,
      -100,
      -100000000,
      5 - 5,
      100 - 95,
      -3 * 8,
      0.00000000000000001,
      -0.0000000000000001,
      pi(),
      my_func(),
      'A short history of everything',
      'Life the universe and everything',
      'Winter is here',
      sqrt(9),
      sqrt(-1)
];

echo 'Testing != (not equal to)<br><br>';

foreach ($a as $b) {
  echo $b, ': ';
  if ($b != 0) {
    echo 'NOT equal to zero';
  } else {
    echo 'Equal to zero';
  }
  echo '<br>';
}

echo '------------------------------------------------------<br><br>';

echo 'Testing > (greater than)<br><br>';

foreach ($a as $b) {
  echo $b, ': ';
  if ($b > 0) {
    echo 'Greater than zero';
  } else {
    echo 'NOT Greater than zero';
  }
  echo '<br>';
}

echo '------------------------------------------------------<br><br>';

echo 'Testing < (less than)<br><br>';

foreach ($a as $b) {
  echo $b, ': ';
  if ($b < 0) {
    echo 'Less than zero';
  } else {
    echo 'NOT Less than zero';
  }
  echo '<br>';
}

Have fun with the script, add some of your own values, and change the test.

It's crucial to understand the difference between what you think you are asking for as opposed to what you are actually asking for. It can cause or save many hours of head scratching.

I will reiterate - a computer does not know or care what it has to do, it will do it regardless; this is where IDEs come in and why they were written, to try and prevent a few silly mistakes being made [repeatedly ] (they can't catch them all, but they do a good job).

 

[Anonymous]

The problem was I didn't add the table prefix, the code should be :

SELECT p.products_id, CASE p.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

NOTE: To find any coding problem more quickly, enable all warnings and errors display, this is what helped me find the missing table prefix.

 

[Anonymous]

Just trying to make you think, computers don't know what you intend to do, they do what you tell them to do... regardless.

It's crucial to understand the difference between what you think you are asking for as opposed to what you are actually asking for. It can cause or save many hours of head scratching.

I will reiterate - a computer does not know or care what it has to do, it will do it regardless; this is where IDEs come in and why they were written, to try and prevent a few silly mistakes being made [repeatedly ] (they can't catch them all, but they do a good job).

Thank you for the explanation, you are right, when coding, we should consider every small details, and this job require us to really slow down and be careful. But you know, sometimes we miss some predefined rules, processed in the server. Have a good day