Using Aggregate Functions in Queries

An aggregate function returns a single result row, based on a group of rows. The group of rows can be an entire table or view. The aggregate functions that SQL supports are listed and described in Oracle Database SQL Language Reference.

Aggregate functions are especially powerful when used with the GROUP BY clause, which groups query results by one or more columns, with a result for each group.

The query in the following example uses the COUNT function and the GROUP BY clause to determine how many people report to each manager. The wildcard character, *, represents an entire record.

Counting the Number of Rows in Each Group

SELECT MANAGER_ID "Manager",
COUNT(*) "Number of Reports"
FROM EMPLOYEES
GROUP BY MANAGER_ID;

Result:

   Manager Number of Reports
---------- -----------------
       100                14
                           1
       123                 8
       120                 8
       121                 8
       147                 6
       205                 1
       108                 5
       148                 6
       149                 6
       201                 1
 
   Manager Number of Reports
---------- -----------------
       102                 1
       101                 5
       114                 5
       124                 8
       145                 6
       146                 6
       103                 4
       122                 8
 
19 rows selected.

The preceding example shows that one employee does not report to a manager. The following query selects the first name, last name, and job title of that employee:

COLUMN FIRST_NAME FORMAT A10;
COLUMN LAST_NAME FORMAT A10;
COLUMN JOB_TITLE FORMAT A10;
 
SELECT e.FIRST_NAME,
e.LAST_NAME,
j.JOB_TITLE
FROM EMPLOYEES e, JOBS j
WHERE e.JOB_ID = j.JOB_ID
AND MANAGER_ID IS NULL;

Result:

FIRST_NAME LAST_NAME  JOB_TITLE
---------- ---------- ----------
Steven     King       President

When used with the DISTINCT option, the COUNT function shows how many distinct values are in a data set.

The two queries in the following example show the total number of departments and the number of departments that have employees.

Counting the Number of Distinct Values in a Set

SELECT COUNT(*) FROM DEPARTMENTS;

Result:

  COUNT(*)
----------
        27

SELECT COUNT(DISTINCT DEPARTMENT_ID) "Number of Departments"
FROM EMPLOYEES;

Result:

Number of Departments
---------------------
                   11

The query in the following example uses several aggregate functions to show statistics for the salaries of each JOB_ID.

Using Aggregate Functions for Statistical Information

SELECT JOB_ID,
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;

Result:

JOB_ID              #    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
 
JOB_ID              #    Minimum    Average     Median    Maximum    Std Dev
---------- ---------- ---------- ---------- ---------- ---------- ----------
PR_REP              1      10000      10000      10000      10000          0
PU_CLERK            5       2500       2780       2800       3100        239
PU_MAN              1      11000      11000      11000      11000          0
SA_MAN              5      10500      12200      12000      14000       1525
SA_REP             30       6100       8350       8200      11500       1524
SH_CLERK           20       2500       3215       3100       4200        548
ST_CLERK           20       2100       2785       2700       3600        453
ST_MAN              5       5800       7280       7900       8200       1066
 
19 rows selected.

To have the query return only rows where aggregate values meet specified conditions, use the HAVING clause.

The query in the following example shows how much each department spends annually on salaries, but only for departments for which that amount exceeds $1,000,000.

Limiting Aggregate Functions to Rows that Satisfy a Condition

SELECT DEPARTMENT_ID "Department",
SUM(SALARY*12) "All Salaries"
FROM EMPLOYEES
HAVING SUM(SALARY * 12) >= 1000000
GROUP BY DEPARTMENT_ID;

Result:

Department All Salaries
---------- ------------
        50      1876800
        80      3654000

The RANK function returns the relative ordered rank of a number, and the PERCENT_RANK function returns the percentile position of a number.

The query in the following example shows that a salary of $3,000 is the 20th highest, and is in the 42nd percentile, among all clerks.

Showing the Rank and Percentile of a Number Within a Group

SELECT RANK(3000) WITHIN GROUP
  (ORDER BY SALARY DESC) "Rank",
ROUND(100 * (PERCENT_RANK(3000) WITHIN GROUP
  (ORDER BY SALARY DESC)), 0) "Percentile"
FROM EMPLOYEES
WHERE JOB_ID LIKE '%CLERK';

Result:

      Rank Percentile
---------- ----------
        20         42

The DENSE_RANK function is like the RANK function, except that the identical values have the same rank, and there are no gaps in the ranking. Using the DENSE_RANK function, $3,000 is the 12^th highest salary for clerks, as the following example shows.

Showing the Dense Rank of a Number Within a Group

SELECT DENSE_RANK(3000) WITHIN GROUP (ORDER BY salary DESC) "Rank"
FROM EMPLOYEES
WHERE JOB_ID LIKE '%CLERK';

Result:

      Rank
----------
        12

Using Aggregate Functions in Queries

Related Topics