SUM quantity of products that belong to the same group (group_id)

57
January 13, 2019, at 08:30 AM

I have a product catalog, each product have 2 quantities (products_quantity and products_incoming_quantity), some products are grouped together under group_id.

I'm trying to give the products that belong to the same group the same quantity, which is the Total of all quantities.

I tried to use GROUP_BY, but I have difficulty writing the mysql code because group_id is in another table.

products table :

|-------------|---------------|-------------------|----------------------------|
| products_id | products_name | products_quantity | products_incoming_quantity |
|-------------|---------------|-------------------|----------------------------|
|      10     |   Product A   |         05        |             02             |
|-------------|---------------|-------------------|----------------------------|
|      11     |   Product B   |         15        |             08             |
|-------------|---------------|-------------------|----------------------------|
|      12     |   Product C   |         12        |             00             |
|-------------|---------------|-------------------|-------------------------

products to group table

|---------------------|-------------------|
|     products_id     |     Group_ID      | 
|---------------------|-------------------|
|          10         |         01        |
|---------------------|-------------------|
|          11         |         01        |
|---------------------|-------------------|

query :

$listing_sql = "select 
    p.products_id, 
    p.products_quantity, p.products_incoming_quantity, 
    (p.products_quantity + p.products_incoming_quantity) AS sub_quantity,
    g.products_id,
    g.group_id      
    FROM " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS . " WHERE
    p.products_id = g.products_id";

I'm trying to add new field "total_quantity" to output the quantity value as this :

Product A total_quantity = 30 Product B total_quantity = 30

Same quantity because they belong to same group.

I need some help to create this query.

Thanks :)

Answer 1

You may join to a subquery which aggregates by group and finds the total quantities:

SELECT
    p.products_id,
    p.products_quantity,
    p.products_incoming_quantity,
    (p.products_quantity + p.products_incoming_quantity) AS sub_quantity,
    t.total_quantity,
    g.products_id,
    g.group_id,
FROM products p
INNER JOIN product_groups g
    ON p.products_id = g.products_id
INNER JOIN
(
    SELECT
        g.group_ID,
        SUM(p.products_quantity + p.products_incoming_quantity) AS total_quantity
    FROM products p
    INNER JOIN product_groups g
        ON p.products_id = g.products_id
    GROUP BY g.group_ID
) t
    ON g.group_id = t.group_id;

Note that I converted your query syntax to use explicit inner joins, instead of old school comma based implicit joins. The way I wrote your query is the preferred way of writing it in modern times.

If you were using MySQL 8+ or later, we might be able to simplify using analytic functions.

READ ALSO
I can not recover data, even by dumping the Wordpress database

I can not recover data, even by dumping the Wordpress database

I recently made a backup of my Wordpress cms database, but now when I restore using the same sql commands in mysql, of success in the dump, however the data of the backup is not recovered, for example the posts, I have more than 100 and did not recover...

30
Difference between two DateTime values -MySql

Difference between two DateTime values -MySql

I am working on an application where I need to get the difference between two columns having data type as DateTime and if the difference is less than one hour, it should give 1 and if greater than 1 and less than 2, it should return 2 and so onI have been trying...

50
YII 1.1 - How to fetch multiple record in FETCH_KEY_PAIR format

YII 1.1 - How to fetch multiple record in FETCH_KEY_PAIR format

We are facing some problem for fetch record in PDO formatSee below my example code:

35
SQL Statement loads very slow with Order By

SQL Statement loads very slow with Order By

I have this sql statement below and returns in 0149seconds:

26