MySQL中具有相似ID的行的列乘法总和 [英] Sum of multiplication of columns for rows with similar IDs in MySQL

问题描述

我在名为purchases"的表中有 3 列:

I have 3 columns in a table called "purchases":

id         amount         price
2          2              21
2          5              9
3          8              5

我想对所有具有相似 ID 的行进行分组，并得到这个数组:

I want to group all rows with similar IDs and have this array as a result:

array([0] => [id => 2, total => 87 (because 2*21+5*9=87)], [1] => [id => 3, total => 40 (because 8*5=40)])

作为具有相似 ID 的行的 SUM(amount*price) 的总帐户.

我试过了

SELECT id, SUM(p.price*p.amount) total FROM purchases p GROUP by p.id

但效果不佳(即它没有达到我想要的，这是我上面写的).关于如何在 mysql 中执行此操作的任何想法?

but it doesn't work well (i.e. it doesn't achieve what I want, which is what I wrote above). Any ideas on how to do this in mysql?

查询返回的示例:

    id         amount         price
    2          3              89
    2          3              19

    SELECT id, SUM(p.price*p.amount) total FROM purchases p GROUP by p.id

==> [id => 2, total => 183]

推荐答案

SELECT
id, 
SUM(amount*price) AS total
FROM mytable
GROUP BY id

数据:

| id | amount | price |
|----|--------|-------|
| 2  | 3      | 19    |
| 2  | 3      | 89    |
| 3  | 203    | 1     |

结果:

id  total
2   324
3   203

这篇关于MySQL中具有相似ID的行的列乘法总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！