Determining Statistical Information
Retrieving Data with Queries
2-24 Oracle Database 2 Day Developer's Guide
You can use basic statistical functions, such as
MIN
,
MAX
,
MEDIAN
,
AVG
, and so on, to
determine the range of salaries across the set. In
, you will examine
salaries grouped by
job_id
, but a similar query could be used to examine salaries
across departments, locations, and so on.
Example 239 Determining Statistical Information
SELECT job_id "Job", COUNT(*) "#", MIN(salary) "Minimum",
ROUND(AVG(salary), 0) "Average",
MEDIAN(salary) "Median", MAX(salary) "Maximum",
ROUND(STDDEV(salary)) "Std Dev"
FROM employees
GROUP BY job_id
ORDER BY job_id;
The results of the query appear.
Job # Minimum Average Median Maximum Std Dev
---------- ---------- ---------- ---------- ---------- ---------- ----------
AC_ACCOUNT 1 8300 8300 8300 8300 0
AC_MGR 1 12000 12000 12000 12000 0
AD_ASST 1 4400 4400 4400 4400 0
AD_PRES 1 24000 24000 24000 24000 0
AD_VP 2 17000 17000 17000 17000 0
FI_ACCOUNT 5 6900 7920 7800 9000 766
FI_MGR 1 12000 12000 12000 12000 0
HR_REP 1 6500 6500 6500 6500 0
IT_PROG 5 4200 5760 4800 9000 1926
MK_MAN 1 13000 13000 13000 13000 0
MK_REP 1 6000 6000 6000 6000 0
...
19 rows selected
Your result shows the statistics for
19
different jobs.
If you use the
HAVING
clause, you can limit your result set to only the kind of values
, you see the salary budget for departments where
the sum of salaries exceeds
$1,000,000
annually.
Example 240 Limiting the Results Using the HAVING Clause
SELECT Department_id "Department", SUM(salary*12) "All Salaries"
FROM employees
HAVING SUM(salary * 12) >= 1000000
GROUP BY department_id;
The results of the query appear.
Department All Salaries
---------- ------------
50 1876800
80 3654000
Your result shows that only two departments have salary budgets in excess of
$1,000,000.
You can use the
RANK
function to determine the relative ordered rank of a number, and
use the
PERCENT_RANK
function to determine the percentile position. In
, you determine these values for a salary of $3,000 over the subset of all
employees who have a '
CLERK
' designation in the
job_id
.
