Group By and Analytic Functions

Aggregating With GROUP BY

SELECT city, COUNT(*) AS members, SUM(orders.total) FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.city
HAVING COUNT(*) >= 1;

Analytic (Window) Functions

SELECT name, total,
       RANK() OVER (ORDER BY total DESC) AS sales_rank,
       SUM(total) OVER (PARTITION BY city) AS city_total
FROM orders o JOIN customers c ON c.id = o.customer_id;

Oracle pioneered analytic functions. They compute a value across a window of rows while keeping each row visible.

Lag and Running Totals

SELECT payment_date,
       amount,
       SUM(amount) OVER (ORDER BY payment_date) AS running_total,
       LAG(amount) OVER (ORDER BY payment_date) AS prev_payment
FROM payments;

Key Points

• GROUP BY summarises; HAVING filters groups.
• Analytic functions keep rows while computing over windows.
• RANK, SUM OVER, LAG and PARTITION BY are core analytics.
• Oracle's analytic SQL strongly influenced the SQL standard.