In mysql, can you divide one alias, by another?

Question

I have a multi-table query, similar to this (simplified version)

SELECT columns, count(table2.rev_id) As rev_count, sum(table2.rev_rating) As sum_rev_rating 
FROM table1
LEFT JOIN table2
ON table1.dom_id = table2.rev_domain_from 
WHERE dom_lastreview != 0 AND rev_status = 1 
GROUP BY dom_url 
ORDER BY sum_rev_rating/rev_count DESC

The problem is in the ORDER BY clause. This causes a mysql error to show, which is as follows:

Reference 'sum_ rev_ rating' not supported (reference to group function)

Answer 1

my mysql is rusty; you might try

SELECT columns, count(table2.rev_id) As rev_count, 
    sum(table2.rev_rating) As sum_rev_rating,
    sum(table2.rev_rating)/count(table2.rev_id) as rev_ratio
FROM table1
    LEFT JOIN table2ON table1.dom_id = table2.rev_domain_from 
WHERE dom_lastreview != 0 
AND rev_status = 1 
GROUP BY dom_url 
ORDER BY rev_Ratio DESC

or

SELECT * from (
    SELECT columns, count(table2.rev_id) As rev_count, 
        sum(table2.rev_rating) As sum_rev_rating 
    FROM table1
        LEFT JOIN table2ON table1.dom_id = table2.rev_domain_from 
    WHERE dom_lastreview != 0 
    AND rev_status = 1 
    GROUP BY dom_url 
) X
ORDER BY X.sum_rev_rating/X.rev_count DESC

or

SELECT * from (
    SELECT columns, count(table2.rev_id) As rev_count, 
        sum(table2.rev_rating) As sum_rev_rating,
        sum(table2.rev_rating)/count(table2.rev_id) as rev_ratio
    FROM table1
        LEFT JOIN table2ON table1.dom_id = table2.rev_domain_from 
    WHERE dom_lastreview != 0 
    AND rev_status = 1 
    GROUP BY dom_url 
) X
ORDER BY rev_Ratio DESC

Answer 2

You're not able to do calculations with aliases. One way of doing this would be to simply create another alias and order by that.

SELECT columns, count(table2.rev_id) As rev_count, sum(table2.rev_rating) As sum_rev_rating, sum(table2.rev_rating)/count(table2.rev_id) as avg_rev_rating
FROM table1
LEFT JOIN table2
ON table1.dom_id = table2.rev_domain_from 
WHERE dom_lastreview != 0 AND rev_status = 1 
GROUP BY dom_url 
ORDER BY avg_rev_rating DESC