CODEWITHSUNDEEP
mysqlsql
Jahidul Islam
Jahidul Islam

Reputation: 119

Is there a way to group SQL functions?

I want to group MIN() and AVG() function in SQL, that is,

Is there a way to do the grouping?

I tried this:

SELECT
      `instructer`.`dept_name`, AVG(`instructer`.`salary`)
AS
      MinAvgSalary
FROM
      `instructer`
GROUP BY
      `instructer`.`dept_name`
HAVING
      AVG(`instructer`.`salary`) = (SELECT MIN(AVG(`instructer`.`salary`)) FROM `instructer` GROUP BY `instructer`.`dept_name`)

But MySQL said:

1111 - Invalid use of group function

This is my instructor table:

Enter image description here

Upvotes: 6

Views: 100

Answers (3)

kjmerf
kjmerf

Reputation: 4335

In case the others don't work, I did this the long way. Create two average salary tables (av1, av2) that hold the average salaries of each department. Then select the minimum salary in av1 in a new table (min1). Then join min1 to av2 on the salary. You should find the record in av2 that has the minimum salary and you can pull the department name from there as well:

SELECT av2.dept_name, av2.avsal
FROM
(SELECT MIN(avsal) as minsal
 FROM
 (SELECT dept_name, AVG(salary) as avsal
  FROM instructor
  GROUP BY dept_name) av1) min1
INNER JOIN
(SELECT dept_name, AVG(salary) as avsal
 FROM instructor
 GROUP BY dept_name) av2
ON min1.minsal = av2.avsal

Tested here: http://sqlfiddle.com/#!9/77b0ac

Upvotes: 0

valex
valex

Reputation: 24134

Try to use LIMIT 1 in the last query:

SELECT
      `instructer`.`dept_name`, AVG(`instructer`.`salary`)
AS
      MinAvgSalary
FROM 
      `instructer`
GROUP BY 
      `instructer`.`dept_name`
HAVING 
      AVG(`instructer`.`salary`) = 
     (SELECT AVG(`instructer`.`salary`) as AvgSalary
        FROM `instructer` 
        GROUP BY `instructer`.`dept_name` 
        ORDER BY AvgSalary LIMIT 1)

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You can use a subselect 

select t.dept_name, min(t.AvgSalary) AS MinAvgSalary 
from  (
  SELECT 
  `instructer`.`dept_name` AS dept_name
  , AVG(`instructer`.`salary`) AS       AvgSalary
  FROM  `instructer`
  GROUP BY `instructer`.`dept_name` ) t
GROUP BY t.dept_name
HAVING t.AvgSalary = min(t.AvgSalary)

Upvotes: 1

Related Questions