Problems with Null in ORDER BY

[Operandi]

Lars suggested a solution to a similar question a few days ago, but I get errors. I want null values to be sorted to the end and not the beginning:

$order_by = " case when average_score is null then 1 else 0 end,average_score DESC, total_scores DESC, number_reviews DESC";


$query = "SELECT mnl_items.item_id, 
                mnl_items.item, 
                mnl_items.description,
                mnl_items.item_url,
                mnl_items.cost, 
                Count( mnl_reviews.review_id ) AS number_reviews,
                Avg( mnl_reviews.review_score ) AS average_score,
                SUM( mnl_reviews.review_score ) AS total_scores
                FROM mnl_items 
                LEFT JOIN mnl_reviews ON mnl_items.item_id = mnl_reviews.item_id AND mnl_reviews.active='yes'
                WHERE mnl_items.cat_id='$cat_id' AND mnl_items.sub_cat_id='$sub_cat_id' AND mnl_items.active='yes' 
                GROUP BY mnl_items.item
                ORDER BY $order_by";

I get the following MySQL error:

Unknown column 'average_score' in 'order clause'

I am using MySQL version 3.23.54

Any suggestions about this?

Thanks, in advance

[Lars_Berg]

You cannot use the label average_score in expressions.

Maybe this will work

$order_by = " case when Avg( mnl_reviews.review_score )
is null then 1 else 0 end,average_score DESC, total_scores DESC, number_reviews DESC";

[Operandi]

Hi Lars. Thanks for the suggestion, but I'm afraid that I get this error:

Invalid use of group function

🙁

[Operandi]

<-- Duplicate Post, by mistake -->

[nnichols]

I haven't tested this but it should work. Give it a try -

SELECT mnl_items.item_id, 
                mnl_items.item, 
                mnl_items.description,
                mnl_items.item_url,
                mnl_items.cost, 
                Count( mnl_reviews.review_id ) AS number_reviews,
                IF(Avg( mnl_reviews.review_score ),Avg( mnl_reviews.review_score ),0) AS average_score,
                SUM( mnl_reviews.review_score ) AS total_scores
                FROM mnl_items 
                LEFT JOIN mnl_reviews ON mnl_items.item_id = mnl_reviews.item_id AND mnl_reviews.active='yes'
                WHERE mnl_items.cat_id='$cat_id' AND mnl_items.sub_cat_id='$sub_cat_id' AND mnl_items.active='yes' 
                GROUP BY mnl_items.item
                ORDER BY average_score DESC, total_scores DESC, number_reviews DESC";

[Operandi]

Thanks very much, nnichols - that worked.

The two great MySQL gurus, nnichols and Lars have rescued me again.

🙂