Oracle PL/SQL: OVER PARTITION BY - This demo returns employees that are making above average salary in their respective department.

Quick Search:

Oracle PL/SQL: OVER PARTITION BY

Jump to:

Category: >> Oracle PL/SQL >> OVER PARTITION BY

<< last

next >>

Snippet Name: OVER PARTITION BY

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

Also see:
» RANK
» REGR_SLOPE
» VARIANCE
» VAR_SAMP
» VAR_POP
» SUM
» STDDEV_SAMP
» STDDEV_POP
» STDDEV
» ROW_NUMBER
» REGR_SYY
» REGR_SXY
» REGR_SXX
» REGR_R2
» REGR_INTERCEPT
» REGR_COUNT
» REGR_AVGY
» REGR_AVGX
» Number Functions: RATIO_TO_REPORT
» Number Functions: RANK
» PERCENTILE_DISC
» PERCENTILE_CONT
» PERCENT_RANK
» NTILE
» MIN
» MAX
» LEAD
» LAST_VALUE
» LAST
» LAG

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 02nd, 2009

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;