Aggregate Functions, GROUP BY, HAVING, and Analytic Functions
Use Oracle DB for summaries, reporting, rankings, running calculations, and analytical-style SQL.
Inside this chapter
- Why Aggregation Matters
- Core Aggregate Functions
- Filtering Aggregated Results
- Analytic Functions for Advanced SQL
Series navigation
Study the chapters in order for the clearest path from Oracle SQL basics to PL/SQL, recovery, tuning, and enterprise operations. Use the navigation at the bottom of each page to move through the full series.
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.