Oracle show all employees with greater than average salary of their department
I don't believe you can refer to a column alias (avg_sal in this case) in a WHERE clause.
You'll need to repeat that inner query, i.e.:
SELECT employee_id, salary, department_id,
(SELECT ROUND(AVG(salary),2)
FROM employees e_inner
WHERE e_inner.department_id = e.department_id) AS avg_sal
FROM employees e
WHERE salary >
(SELECT ROUND(AVG(salary),2)
FROM employees e_inner
WHERE e_inner.department_id = e.department_id)
ORDER BY avg_sal DESC
Not great, with those two inner queries, but that's the most-straightforward way to correct the error.
Update: Haven't tested this, but try the following:
SELECT e.employee_id, e.salary, e.department_id, b.avg_sal
FROM employees e
INNER JOIN
(SELECT department_id, ROUND(AVG(salary),2) AS avg_sal
FROM employees
GROUP BY department_id) e_avg ON e.department_id = e_avg.department_id AND e.salary > e_avg.avg_sal
ORDER BY e_avg.avg_sal DESC
Write a SQL code to select all employees with salary greater than the average department salary and also greater than $30K
This may work
select s1.employeeid, s1.departmentid, s1.salary
from salary as s1
inner join
(select departmentid, avg(salary) as avgsalary
from salary
group by departmentid) as s2
on s1.departmentid = s2.departmentid
where salary > avgsalary and salary > 30000
;
SQL - Trying to get employees with greater than average department salary
Window functions are probably the best solution -- as in @zealous's answer. However, a subquery is also very reasonable. The idea is:
select e.first_name, e.salary, e.department_id
from employees as e
where e.salary > (select avg(e2.salary)
from employees e2
where e2.department_id = e.department_id
);
Here is a db<>fiddle.
Key ideas:
- You don't need to
join
todepartments
. Just because you have the idea doesn't mean thejoin
is necessary. - The
GROUP BY
is unnecessary in the subquery. The correlation clause takes care of that.
How to check if a value is larger than the average of these values?
Use AVG
as an analytic function:
SELECT full_name
FROM (
SELECT e.first_name || ' ' ||e.last_name AS full_name,
e.salary,
AVG( e.salary ) OVER ( PARTITION BY l.country_id ) AS avg_salary
FROM hr.employees e
INNER JOIN HR.departments d
ON e.department_id = d.department_id
INNER JOIN HR.locations l
ON d.location_id = l.location_id
)
WHERE salary > avg_salary;
How to select employees with salary less then the average of their departament using SQL analytical functions?
Use a window function and a subquery:
select e.*
from (select e.*,
avg(salary) over (partition by id_dep) as avg_salary
from employees e
) e
where salary < avg_salary;
You cannot use window functions in where
or having
clauses. Some databases support a qualify
clause that filters on window functions; but Oracle does not.
Related Topics
The Object Name Contains More Than the Maximum Number of Prefixes. the Maximum Is 3
Select Columns with Particular Column Names in Postgresql
Format a Number with Commas But Without Decimals in SQL Server 2008 R2
Storing Multiple Choice Values in Database
Pivot - SQL - Values from Subquery
SQL Server Convert Datetime into Another Timezone
What Is the Resource Impact from Normalizing a Database
Incorrect Parameter Count in the Call to Native Function 'Datediff'
SQL Query to Select the 'Next' Record (Similar to First or Top N)
Query for Searching the Name Alphabetically