Need Help on nested query

[woonm]

Hi;
I am quite new to mySQL. I run a simple subquery on mySQL 3.23.54 and the statement is:
------------------------------------------------
SELECT Products.ProductName, Products.UnitPrice
FROM Products
WHERE Products.UnitPrice > (SELECT AVG(Products.UnitPrice) FROM Products)
ORDER BY Products.UnitPrice DESC;
-----------------------------------------
The error message stated
" an error in your SQL syntax near 'SELECT(SUM('MS2'.'UnitPrice')/COUNT('MS2'.'UnitPrice')) from 'Products'' at line 1(#1064)"
-----------------------------------------------
I switch the subquery "(SELECT AVG(Products.UnitPrice) FROM Products)" with a scalar and it worked. The subquery worked by itself.

I tried to add "ANY" or "ALL" before the relational operator ">" and none of them worked.

Thanks for any inputs and correction.

 

[swampBoogie]

Your query is correct, it is just that Mysql is deficient in basic SQL capacities. In short, subqueries are not supported. You need to do it in two steps.

 

[woonm]

Thanks for the hints. I did separate the query into two and it works now.

Thanks swampBoogie!