将SUM()添加到mySQL JOIN查询 [英] adding SUM() to mySQL JOIN query
问题描述
我想将2个数据库连接在一起，并从两个表中得出具有相同ID的每个数据库的总和，如果您知道im来自...
I want to join 2 databases together and work out the sum of each one with the same ID from both tables, if you get where im coming from...
我有一个ID为product_name，product_description的数据库 另一个具有ID，库存量，日期已添加
I have one database with ID, product_name, product_description and the other has ID, stock amount, date_added
我已经使用JOIN函数将数据库连接在一起，并且可以正确显示它们，但是对于数据库中的每个条目，当我添加库存时，它都会添加另一行，并在前端显示另一行.我希望将所有相同的ID分组为一行.
i have joined the databases together using the JOIN function and it displays them correctly, but for each entry into the database it adds another row when i add stock and shows another row at the frontend. I would like it to group all of the same id's into one row.
我已经尝试过了:
$result = mysql_query("SELECT * FROM site_products JOIN site_trans ON site_products.product_id = site_trans.trans_product GROUP BY site_products.product_id");
它将相同的ID分组为一行，但是库存量仅显示最后添加的数量，而不是总数量.
it groups the same id's together into one row, but the stock amount just displays the last amount added, not the total amount.
我当时正在考虑将SUM()添加到此，但是我不确定它在查询中的位置.
i was thinking about adding the SUM() to this but im not sure where it would go in the query.
推荐答案
The GROUP BY clause will gather all of the rows together that contain data in the
specified column(s) and will allow aggregate functions to be performed on the
one or more columns.
您可以在以下链接中了解有关GROUP BY的汇总函数用法的更多信息:
You can read more about GROUP BY with aggregate functions usage in following links:
- w3schools.com
- SQL: GROUP BY Clause
- GROUP BY clause
- MySQL GROUP BY - Aggregate Functions
现在，我认为理解下面的查询可以解决您的问题很简单:
Now I think it will be simple to understand the following query which solves your problem:
SELECT
site_products.product_id,
SUM(stock amount) AS total_amount
FROM site_products JOIN site_trans
ON site_products.product_id = site_trans.trans_product
GROUP BY site_products.product_id
这篇关于将SUM()添加到mySQL JOIN查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
