KnowledgeHub
Questions
Tags
Users
Search
Alex Rivera
|
Logout
Edit Question
Title
Body
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]
Tags (comma-separated)
Save Edits
Cancel