Error in query

[piyush23424]

I am trying to run this query but it is giving error. Please tell me what is wrong with this query ?

 

$qry  = "SELECT * FROM `products` where price > avg(price)"

 

One more question : can we use group by clause on two columns :

eg : Select name from students group by subject, name;

[Maq]

What is the error?

[piyush23424]

What is the error?

 

 

For the first query error is :

 

Invalid use of group function

 

For the second query :

 

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

[jagdish kothapalle]

1. For the query,

 

$qry  = "SELECT * FROM `products` where price > avg(price)"

 

try the below one

 

$qry  = "SELECT * FROM products where price > (select avg(price) from products)"

 

The mysql engine doesn't understand avg(price) unless you have a variable something like $avgprice (which is already assigned)  passed from your php.

 

2. Ideally, you can use multiple fields in group by clause provided they are also present in the select clause.