salary of each employee along with the average salary of department in sql-CodePudding

SELECT 
    job_id, emp_name, salary, AVG(SALARY) AS AVERAGE_SALARY 
FROM 
    employees 
GROUP BY 
    emp_name, department_id;

I've tried this but this doesn't seem to work.

table: https://i.stack.imgur.com/3jB6x.png

output : https://i.stack.imgur.com/q7R5T.png my output: https://i.stack.imgur.com/EfxcZ.png

CodePudding user response：

You seem to want a window average:

select 
    job_id, emp_name, salary,  
    avg(salary) over(partition by department_id) as avg_dpt_salary
from employees

This gives you one row per employee, along with the average salary of all employees of the same department.

CodePudding user response：

You're not grouping by salary, yet you want the average. In this case the non-averaged salary column will return a value from a single record if it even works at all.

There's a couple ways to interpret what you're asking for.

Perhaps you want a report of each employee, but you want the average for the entire department. I believe you're grouping by emp_name because you want each employee's name in the output. You could do that this way in MariaDB:

select
   job_id, emp_name, salary, (
      select avg(salary) from employees b where b.department_id = a.department_id
   ) as avg_salary
from
   employees a;

The other way to interpret what you're asking is if the same employee has multiple entries in the database and you only want one entry per employee name. The problem there, is what value are you expecting for the non-averaged salary? I've excluded that in the next example since it doesn't make sense to me.

select
   emp_name, department_id, avg(salary) as avg_salary
from
   employees a;
group by
   emp_name, department_id;

I'm not certain either of these actually generate the data you're looking for, so please give me feedback on which of these is closer to what you're looking for and I'll edit the answer if necessary.