MYSQL and the ORDER BY clause..

[tob]

Hi guys,

I'm trying to order by a field that is nested arithimetic..

SELECT Keyword, (sum(Converted) / sum(Clicked)), (sum(Clicked) / count(keyword)) FROM tobyn_stats WHERE date = '10/07/2007' and Source = 'GC' GROUP BY Keyword ORDER BY (sum(Converted) / sum(Clicked)) DESC

I get what seems like random ordering results, there is something wrong with the order by clause... possibly because some are null, but I think it has something to do with the math.. can someone check this out and let me know?!

Thanks!!

Tob

 

[threehundred]

SELECT Keyword, (sum(Converted) / sum(Clicked)) AS newsum, (sum(Clicked) / count(keyword)) FROM tobyn_stats WHERE date = '10/07/2007' and Source = 'GC' GROUP BY Keyword ORDER BY newsum DESC

I believe this should work. In the SELECT, you are telling SQL to call make the variable 'newsum' equal to (sum(converted) / sum(clicked)), then it orders by that new variable.

 

[tob]

Beautiful! I could have sworn I tried that, but guess not.. thanks for your help!

Tob