How to properly write sub querys that use math functions to compute column in the face of "Error Code 1248" Derived Tables must have their own Alises?

Question

I being asked in exercise to calculate three values from two columns, List_price and discount_amount. partly some of my issue is that my programming instincts want to kick in hindering my ability to fully grasp what is being taught.

Can someone show me what is wrong with the code and provide for a template to better handle the situation.

SELECT list_price, discount_percent, product_name, raw_percent,
    discount_amount, (list_price - discount_amount) AS discount_price 
FROM ( 
     SELECT discount_percent,
                    list_price,
                    (discount_percent /10) = raw_percent 
                    (list_price * raw_percent) = discount_amount    
) ORDER BY discount_price DESC LIMIT 5; 

Answer 1

Derived Tables must have their own Aliases

MySQL has a requirement that, if you are looking to access the columns of the resultset returned by a subquery, then the subquery should be properly aliased in the outer query (even if that alias is not actually used in the query).

You would need to alias the subquery that you are using in the WHERE clause :

SELECT ...
FROM (
    SELECT ...
) AS some_alias
ORDER BY discount_price DESC LIMIT 5; 

Other remark regarding your query :

• column « product_name » should be returned by the subquery, otherwise it will not be available in the outer query
• there is a missing coma in the subquery after « raw_percent »