Combining two tables to sort by costs minus expenses - need complex order by statement?

I'm a little stuck on what I'm pretty sure is an easy solution. Here's the dilemma:

I have two mysql tables:

one for cars (with the car_price_purchase / car_price_sold for each car_id) one is for expenses (with each expense attached to a car - expense_amount, expense_car_id )

What I need to do is add up all the expenses for each car then subtract that plus the price sold for from the price purchased for in order to get the overall profit.

This is quite easy to do in general but I want to sort by the end profit amount.

Do I just store these results in an array and then do a sort on that array?? Or can I write a complex joined mysql query with a even more c开发者_运维百科omplex order by statement??

Any help is appreciated. thanks nicholas

Are you currently doing the math to determine the end profit amount as part of the query as well, or afterward in php? I would suggest doing the math as part of the query, allowing you to do the order by there, ie:

SELECT c.car_id, 
(c.car_price_sold - (IFNULL(SUM(e.expense_amount), 0) + c.car_price_purchase)) as profit 
FROM cars c
LEFT JOIN expenses e ON e.expense_car_id = c.car_id 
GROUP BY c.car_id 
ORDER BY profit

Here is the group by code I found that looks promising:

SELECT user_1.username, Count(user.refid) AS CountOfrefid
FROM [user] LEFT JOIN [user] AS user_1 ON user.refid = user_1.userid
GROUP BY user_1.username order by Count(user.refid);

Still need to decipher it though :) and see if it works

thanks nicholas