从MySQL中的表中选择并添加两列相乘的结果(Select and add result of multiplying two columns from table in MySQL)

从MySQL中的表中选择并添加两列相乘的结果(Select and add result of multiplying two columns from table in MySQL)

我正在尝试组合MySQL查询来从特定表中选择和乘以两列。 另外，如果第三列的值与任何其他行匹配，我希望查询添加每个乘法的结果。

该表包含以下列： id ， client_id ， project_id ， rate和quantity 。

所以寻找rate乘以每一行的quantity 。 然后，如果每一行都具有相同的project_id则添加这些结果。

这是我到目前为止：

SELECT rate * quantity AS total_price FROM orders WHERE client_id=1 GROUP BY project_id

这似乎让我很接近，但并不是一直都在那里。 它似乎将所有计算结果返回到一个大数组中，而不是将相关的结果添加到一起。 更复杂的是，有几个项目具有相同的client_id 。

I'm trying to put together a MySQL query to select and multiply two columns from a specific table. In addition, I'd like the query to add the results of each multiplication if the value of a third column match any other rows.

The table has these columns: id, client_id, project_id, rate, and quantity.

So looking for rate multiplied by quantity for each row. Then add those results if every row has the same project_id.

Here is what I have so far:

SELECT rate * quantity AS total_price FROM orders WHERE client_id=1 GROUP BY project_id

It seems to be getting me close, but not quite all the way there. It seems to be returning all the computed results in one big array, without adding related ones together. To complicate things more, there are several projects with the same client_id.

最满意答案

只需添加Sum聚合

SELECT project_id, Sum(rate * quantity) AS total_price FROM orders WHERE client_id = 1 GROUP BY project_id

Just add Sum aggregate

SELECT project_id, Sum(rate * quantity) AS total_price FROM orders WHERE client_id = 1 GROUP BY project_id