Sortby the sum of two different table INTs

[mikenco]

I have joined a few tables together and all seems to work well..

SELECT * FROM products
JOIN underwriters ON (products.underwriter_lookup = underwriters.id_uw)
JOIN countries ON (products.country_lookup = countries.id_co)
JOIN repay_types ON (products.repay_type_lookup = repay_types.id_repay)
JOIN base_rates ON (products.base_rate_lookup = base_rates.base_rates_id)

However, I have been asked if I can now sort some of the output into numerical order.

There is a row in the "Products" table called "initial_rate" which is an INT
In the "Base Rates" Table "base_rates_amount" which is also an INT

What I need to do is sort the query kind of like this to give a total interest rate..

ORDER BY products.initial_rate + base_rates.base_rates_amount

But this doesn't work.

I have been scouring Google for solutions, but the ones I have found are too simplistic and don't include JOINS etc..

This is just beyond my experience right now and would really appreciate any pointers.

Thanks

Mike

[Josh Connerty]

Does it work if you only order by one of the terms by removing the + and the following statement?

[mikenco]

No, I can't get any results when using ORDER BY at all..

ORDER BY products.initial_rate

ORDER BY (products.initial_rate)

ORDER BY `products.initial_rate`

[mikenco]

Ok, I kinda got it working with this.. but it only pulls a single record, but there are at least 10 'best buys'..

$bareQuery = "SELECT * FROM (products)
				JOIN underwriters ON (products.underwriter_lookup = underwriters.id_uw)
				JOIN countries ON (products.country_lookup = countries.id_co)
				JOIN repay_types ON (products.repay_type_lookup = repay_types.id_repay)
				JOIN base_rates ON (products.base_rate_lookup = base_rates.base_rates_id)
				WHERE (products.best_buy = '1')
				ORDER BY SUM(products.initial_rate+base_rates.base_rates_amount)
				";

Any idea why it is displaying only one record instead of all of them?

[mikenco]

RESOLVED!.. I simply dropped off the SUM part and it now works.. go figure :)

ORDER BY (products.initial_rate+base_rates.base_rates_amount) LIMIT 0, 20