CODEWITHSUNDEEP
mysqlaggregate-functions
Ajay Kulkarni
Ajay Kulkarni

Reputation: 3039

Invalid use of group function in mysql

I've a table like this: 

+----+------+--------+
| id | name | salary |
+----+------+--------+
|  1 | Ajay |  20000 |
|  2 | Aja  |   2000 |
|  3 | Aj   |    200 |
|  4 | A    |   3000 |
|  5 | q    |  30000 |
+----+------+--------+

I want to write a query that can print highest salary, medium salary and lowest salary. So I wrote this query: 

select salary 
  from parent
 where max(sal) 
    && salary < ( SELECT MAX( salary )
                   FROM parent ) 
    && min(salary);

And mysql returned an error: 

ERROR 1111 (HY000): Invalid use of group function

what is the correct query?

Upvotes: 0

Views: 79

Answers (2)

O. Jones
O. Jones

Reputation: 108641

MySQL doesn't offer an aggregate function to grab a median value, sorry to say. Hopefully you can go with the average (the arithmetic mean) value.

Stuff like MAX(), MIN(), and AVG() (called aggregate functions in the jargon of SQL) can't appear in WHERE clauses. They can show up in SELECT and HAVING clauses. That's why you got an error.

You'll be wanting

SELECT MAX(salary) max_salary, 
       AVG(salary) avg_salary,
       MIN(salary) min_salary
  FROM parent

If you control your own MySQL server and you have a few MySQL dba chops, you can install a user-defined function to compute the median value. (If you're using a hosting service with a multitenant MySQL server, forget about this.) Read this.

http://mysql-udf.sourceforge.net/

Upvotes: 2

verhie
verhie

Reputation: 1318

select 'min' as k, min(salary) as v from parent union select 'avg' as k, avg(salary) as v from parent union select 'max' as k, max(salary) as v from parent

Upvotes: 0

Related Questions