Aggregate Functions, GROUP BY, HAVING, and Analytic Functions

Why Aggregation Matters

Business teams want totals, averages, status counts, top performers, trends, and grouped summaries. Oracle SQL can compute these directly and efficiently close to the data.

Core Aggregate Functions

SELECT
    order_status,
    COUNT(*) AS total_orders
FROM orders
GROUP BY order_status;

Functions like COUNT, SUM, AVG, MIN, and MAX are the building blocks of reporting SQL.

Filtering Aggregated Results

SELECT
    customer_id,
    COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 5;

HAVING filters grouped results after aggregation, while WHERE filters detail rows first.

Analytic Functions for Advanced SQL

SELECT
    order_id,
    customer_id,
    order_date,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date DESC
    ) AS customer_order_rank
FROM orders;

Oracle analytic functions are extremely powerful. They support ranking, running totals, lag/lead comparisons, and advanced reporting patterns without collapsing all detail rows. These are essential for advanced SQL work.