Over Partition By

Description: This demo returns employees that are making above average salary in their respective department.

NTILE (<expression>) OVER ([query_partition_clause] <order BY clause>)
 
conn hr/hr
 
col ename format a30
col department_name format a20
 
SELECT * FROM (
  SELECT e.ffirst_name || ' ' || e.last_name ENAME, d.department_name,
  e.salary, TRUNC(e.salary - AVG(e.salary) OVER (PARTITION BY
  e.department_id)) sal_dif
FROM employees e, departments d
WHERE e.department_id=d.department_id)
WHERE sal_dif > 0
ORDER BY 2,4 DESC;

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.