Sum of column multiplication for rows with identical identifiers in MySQL

Question

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 identifiers and get this array:

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

as a total SUM account (amount * price) for rows with similar identifiers .

I tried using

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

but it does not work (i.e. does not achieve what I want, 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] 

Answer 1

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

Data:

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

Result:

 id total 2 324 3 203 

Answer 2

@sombe: I just tested your request and it works fine. Are you sure your mysql is updated?

The second one works fine: