SQL Select * from table sum and group by id

本文关键字：and group by id sum table Select from SQL | 更新日期: 2023-09-27

我需要一个小帮助来分组和汇总项目id的数量。

Data from database:

products
id  code    name            price
1   1234    Product name    12
2   1235    Product name    12
3   1236    Product name    12
warehouses_products
product_id   warehouse_id   quantity
1           1               12
2           1               0
2           2               3
3           1               1
3           2               1

PHP获取数据

$db = new PDO('mysql:host=localhost;dbname=data2;charset=utf8', 'user2', 'Password');
foreach($db->query('SELECT * FROM products as pr INNER JOIN warehouses_products AS s ON (s.product_id = pr.id) group by pr.id') as $row) {
echo $row["code"] . ' ' . $row["quantity"];
}

一个例子:如果我在两个仓库上有相同的代码，这只得到其中的一个，我想要的是将数量相加并按product_id分组。

试试这个:

'SELECT *, SUM(s.quantity) AS sum_quantity FROM products as pr INNER JOIN warehouses_products AS s ON (s.product_id = pr.id) group by pr.id'

在你的foreach:

echo $row["code"] . ' ' . $row["sum_quantity"];

查询:

SELECT p.id, p.code, SUM(wp.quantity) as quantity
FROM products p
JOIN warehouses_products wp ON wp.product_id = p.id
GROUP BY p.id, p.code

PHP:

$db = new PDO('mysql:host=localhost;dbname=data2;charset=utf8', 'user2', 'Password');
foreach($db->query('SELECT p.id, p.code, SUM(wp.quantity) as quantity FROM products p JOIN warehouses_products wp ON wp.product_id = p.id GROUP BY p.id, p.code') as $row) {
echo $row["code"] . ' ' . $row["quantity"];
}

请记住，我们依赖于product表中的代码和id是唯一对的事实。