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

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

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)])

as total accounts for SUM(amount*price) for rows with similar IDs.

I've tried using

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

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?

An example of what the query returns:

    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]
mysql select sum
Edit
Report