Sum values from the same column where ids are similar

Codes here !

Moderators: egami, macek, gesf

Post Reply
psytanium
New php-forum User
New php-forum User
Posts: 7
Joined: Mon Apr 22, 2019 2:07 am

Sat Apr 27, 2019 8:06 am

'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

Code: Select all

$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 :

Code: Select all

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

User avatar
hyper
php-forum Fan User
php-forum Fan User
Posts: 843
Joined: Mon Feb 22, 2016 5:52 pm

Sat Apr 27, 2019 10:43 am

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

You could use this as a sub query

Code: Select all

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

psytanium
New php-forum User
New php-forum User
Posts: 7
Joined: Mon Apr 22, 2019 2:07 am

Sat Apr 27, 2019 12:41 pm

hyper wrote:
Sat Apr 27, 2019 10:43 am
If you want to select only products where product_master is not zero

You could use this as a sub query

Code: Select all

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 ?

User avatar
hyper
php-forum Fan User
php-forum Fan User
Posts: 843
Joined: Mon Feb 22, 2016 5:52 pm

Sun Apr 28, 2019 5:14 am

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:

Code: Select all

<?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 &gt; (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 &lt; (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 :oops: ] (they can't catch them all, but they do a good job).

psytanium
New php-forum User
New php-forum User
Posts: 7
Joined: Mon Apr 22, 2019 2:07 am

Sun Apr 28, 2019 8:04 am

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.
Last edited by psytanium on Sun Apr 28, 2019 8:29 am, edited 1 time in total.

psytanium
New php-forum User
New php-forum User
Posts: 7
Joined: Mon Apr 22, 2019 2:07 am

Sun Apr 28, 2019 8:08 am

hyper wrote:
Sun Apr 28, 2019 5:14 am

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 :oops: ] (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 :)

Post Reply