Invalid use of group function in mysql

Question

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?

what do you try to achieve?

Alex
– Alex

2015-05-01 12:54:16 +00:00
Commented May 1, 2015 at 12:54 — Alex
– Alex, Commented May 1, 2015 at 12:54

O. Jones · Accepted Answer · 2015-05-01 13:18:43Z

2

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/

edited May 1, 2015 at 13:18

answered May 1, 2015 at 12:54

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Strawberry Over a year ago

As an aside, median UDFs and sprocs are available.

verhie · Accepted Answer · 2015-05-01 12:50:42Z

0

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

answered May 1, 2015 at 12:50

verhie

1,3181 gold badge7 silver badges7 bronze badges

Collectives™ on Stack Overflow

Invalid use of group function in mysql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related