Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 38 ยท Functions and Expressions

Aggregate and Analytic Functions

Summarize data in Oracle with GROUP BY, HAVING, LISTAGG and ROLLUP, then keep every row with analytic functions: RANK, ROW_NUMBER, LAG, LEAD and running totals.

Aggregate functions collapse many rows into one number: a count, a total, an average. Analytic functions (also called window functions) compute the same kind of figures but keep every row, so you can show each employee next to their department's average, or rank sales without losing the detail. Oracle introduced analytic functions in 8i, long before most databases, and they are central to Oracle reporting.

The portable parts run in the editor. LISTAGG and ROLLUP are Oracle's spelling and are shown read-only, each followed by a stand-in.

Aggregates and GROUP BY

The core aggregates are COUNT, SUM, AVG, MIN and MAX. All except COUNT(*) ignore NULL. COUNT(*) counts rows; COUNT(col) counts rows where col is not NULL; COUNT(DISTINCT col) counts distinct values.

GROUP BY splits the rows into groups and computes the aggregate for each group. HAVING filters the groups (after aggregation), where WHERE filters rows (before):

SQL GROUP BY and HAVING ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The rule Oracle enforces strictly: every column in the SELECT list must be either inside an aggregate or listed in GROUP BY, or you get ORA-00979: not a GROUP BY expression. Oracle 23ai lets you use column aliases in GROUP BY and HAVING; older versions repeat the expression.

Aggregates can be nested one level in Oracle, a rarely used extra: SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id returns the highest department average.

LISTAGG: a list in one cell

LISTAGG joins values from many rows into one string:

SQL
SELECT department_id,
       LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS people
FROM employees
GROUP BY department_id;

The WITHIN GROUP (ORDER BY ...) clause sets the order inside the string. If the result would exceed the VARCHAR2 limit, LISTAGG raises ORA-01489; from 12.2 you can write LISTAGG(last_name, ', ' ON OVERFLOW TRUNCATE '...') WITHIN GROUP (...) to cut it instead. Stand-in with SQLite's group_concat:

SQL Stand-in for LISTAGG ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

ROLLUP, CUBE and GROUPING SETS

Reports usually need subtotals. ROLLUP adds them in one pass:

SQL
SELECT department_id, job_id, SUM(salary) AS payroll
FROM employees
GROUP BY ROLLUP (department_id, job_id);

The result has a row per department-and-job pair, a subtotal row per department (where job_id is NULL), and a grand total row (both NULL). GROUPING(col) returns 1 on rows where that column is a subtotal marker, so you can label them instead of showing NULL. CUBE produces every combination of subtotals, and GROUPING SETS ((a), (b)) lets you list exactly the groupings you want.

Stand-in: UNION ALL of the levels gives the same rows:

SQL Stand-in for ROLLUP ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Analytic functions

An analytic function is written function(...) OVER (PARTITION BY ... ORDER BY ... frame). PARTITION BY splits rows into independent groups (like GROUP BY, but rows stay), ORDER BY orders the rows inside each partition, and the frame limits which rows the function sees.

flowchart LR
  A["Rows"] --> B["PARTITION BY<br/>department"]
  B --> C["ORDER BY<br/>salary DESC"]
  C --> D["Function per row<br/>RANK, SUM, LAG"]

Analytic functions compute a value for each row from its partition.

Ranking

Function Ties Example for salaries 90, 80, 80, 70
ROW_NUMBER() Unique numbers always 1, 2, 3, 4
RANK() Same rank, then a gap 1, 2, 2, 4
DENSE_RANK() Same rank, no gap 1, 2, 2, 3
SQL
SELECT last_name, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept
FROM employees;
SQL Ranking inside groups ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The top-N-per-group pattern wraps the ranking in a subquery, because you cannot filter on an analytic function directly in WHERE:

SQL
SELECT * FROM (
  SELECT last_name, department_id, salary,
         ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employees
) WHERE rn <= 2;

Running totals, LAG and LEAD

Add an aggregate with an ORDER BY inside OVER to get a running value, and use LAG and LEAD to look at the previous or next row:

SQL Running total, LAG and LEAD ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

LAG(expr, offset, default) returns the value offset rows back, or default at the start. This identical syntax runs in Oracle.

Frames: ROWS and RANGE

The default frame for an ordered window is "from the start to the current row" (with RANGE, so ties are included). To make a moving average, spell the frame out: AVG(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). Without an ORDER BY, the window is the entire partition, which is how you show the group average next to each row:

SQL Each row next to its group's average ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

KEEP (DENSE_RANK FIRST/LAST)

Oracle has a special aggregate form for "the value from the row with the highest X":

SQL
SELECT department_id,
       MAX(last_name) KEEP (DENSE_RANK FIRST ORDER BY salary DESC) AS top_earner
FROM employees
GROUP BY department_id;

It returns, per department, the last name of the best-paid employee (using MAX only to break ties) without a self-join. Many teams use ROW_NUMBER() instead, which is easier to read.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the difference between WHERE and HAVING?

    WHERE removes rows before GROUP BY runs. HAVING removes whole groups using aggregate results.
  2. 2. For values 90, 80, 80, 70 sorted descending, what does RANK() return for 70?

    RANK leaves a gap after ties: 1, 2, 2, 4. DENSE_RANK would give 3.
  3. 3. Which clause lets analytic functions restart for each department?

    PARTITION BY splits rows into independent groups for the function while keeping each row.
  4. 4. Why does WHERE ROW_NUMBER() OVER (...) <= 3 fail?

    Analytic functions are computed after WHERE and GROUP BY. Put them in a subquery and filter in the outer query.
  5. 5. What does LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) produce?

    LISTAGG concatenates values from the rows of each group into one string.

Summary

  • COUNT, SUM, AVG, MIN and MAX summarize groups; HAVING filters groups, WHERE filters rows.
  • LISTAGG builds a delimited list; ROLLUP, CUBE and GROUPING SETS add subtotals.
  • Analytic functions OVER (PARTITION BY ... ORDER BY ...) keep every row.
  • ROW_NUMBER, RANK and DENSE_RANK handle ties differently; filter them in an outer query.
  • LAG, LEAD and running sums look across neighboring rows.

Next: Joins in Oracle