Aggregate Functions, GROUP BY, HAVING, and Window Functions

Aggregation for Reporting

Most applications need summaries, not just raw rows. Managers want total revenue, counts by status, averages by region, and trends over time. PostgreSQL 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 base of reporting SQL.

Using HAVING

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

WHERE filters rows before grouping, while HAVING filters grouped results. That distinction is fundamental in SQL reporting work.

Window Functions for Advanced Analysis

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;

Window functions are a major PostgreSQL strength. They let you rank, compare, and calculate running values without collapsing rows the way standard aggregation does. They are extremely useful in analytics, finance, operational dashboards, and interview scenarios.