Jump to content


Photo

Complex MySQL Query Help


  • Please log in to reply
1 reply to this topic

#1 Lucas3677

Lucas3677
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 30 September 2005 - 08:19 PM

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.

#2 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 30 September 2005 - 09:56 PM

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--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users