Complex MySQL Query Help

[Lucas3677]

I have 3 tables:

 

member_items id (not important), item_id (= items.id), owner_id (= members.id), amount

items id, name, price

members id, username

 

What I am trying to do here is add up all of a users item values (amount * price) and sort users accordingly from most assets to least. I have started the query, but I don't really know where to go from here. At this point, it doesnt add all the items together, it just gets the last item found for each user

 

SELECT member_items.amount * items.price AS TOTAL, member_items.owner_id FROM member_items LEFT JOIN items ON member_items.item_id = items.id WHERE member_items.amount > 0 GROUP BY member_items.owner_id;

 

Thanks in advance.

[ryanlwh]

Untested

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] SUM(member_items.amount * items.price) AS TOTAL, member_items.owner_id FROM member_items LEFT JOIN items ON member_items.item_id = items.id GROUP BY members.id; [!--sql2--][/div][!--sql3--]