I have a database with a large number of columns that are numerical and a small number of columns that are text.

I would like to group by name and show average salary so my SQL query is:

select
     name,
     avg(salary),
     department
from
     table
group by
     name;

My question is: What dictates what is going to appear in the department column, and are there any functions where I can control that output? For the above example, if I wanted to make sure the department shown was wherever their salary was greatest, could I force that?

CodePudding user response：

here is one way :

select
     name,
     avg(salary),
     first_value(department) over (order by salary desc) department
from table
group by name;

CodePudding user response：

What dictates what is going to appear in the department column, and are there any functions where I can control that output?

With sql_mode ONLY_FULL_GROUP_BY enabled (which it is by default, from 5.7 on), selecting just department will result in a 1055 error. Without ONLY_FULL_GROUP_BY, an arbitrary department from one of the grouped rows will be returned, and you should not count on it being any particular one. Just changing to min(department) or max(department) enables the query to work with ONLY_FULL_GROUP_BY, if you don't actually care which department is returned.

CodePudding user response：

For old MySQL versions (have not window functions) you can use sub-query;

select
     name,
     avg(salary),
     (select max(department) from eployees_salary d where d.name = eployees_salary.name and d.salary = max(eployees_salary.salary)) department
from eployees_salary
group by name;

MySQL sub-query online

Since MySQL 8.0 window functions can be used:

with d as (
    select name, salary, first_value(department) over (partition by name order by salary desc) department
    from eployees_salary
)
select 
     name,
     avg(salary),
     min(department) department
from d
group by name;

MySQL 8.0 window functions online