Real-world examples of SQL aggregate functions you’ll actually use

Let’s start with the fun part: concrete queries. When people ask for examples of examples of SQL aggregate functions, they usually want to see how these functions behave on realistic schemas, not abstract foo and bar tables.

Below, assume a simple but familiar schema:

-- Orders table
orders (
  order_id       BIGINT PRIMARY KEY,
  customer_id    BIGINT,
  order_date     DATE,
  status         VARCHAR(20),   -- e.g. 'PAID', 'CANCELLED'
  total_amount   NUMERIC(10,2),
  store_id       INT
);

-- Customers table
customers (
  customer_id    BIGINT PRIMARY KEY,
  country        VARCHAR(2),    -- ISO country code
  signup_date    DATE
);

We’ll keep everything grounded in this kind of structure so each example of an aggregate function feels like something you might paste into a real dashboard query.

Revenue rollups: SUM and COUNT with GROUP BY

One of the most common examples of SQL aggregate functions in the wild is a revenue report by month. You’re not just counting rows; you’re aggregating money, orders, and customers.

SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*)                        AS total_orders,
  COUNT(DISTINCT customer_id)     AS unique_customers,
  SUM(total_amount)               AS gross_revenue
FROM orders
WHERE status = 'PAID'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Why this matters in 2024–2025:

• This pattern shows up in virtually every BI tool and analytics stack.
• COUNT and SUM are usually pushed down and optimized by columnar engines like Snowflake and BigQuery.
• Distinct-count logic is often the first place performance pain shows up at scale.

This is one of the best examples of how aggregate functions turn raw events into something business teams can read at a glance.

Average order value: AVG, but with real-world caveats

Another classic example of an aggregate is average order value (AOV). It sounds trivial until you realize refunds, test orders, and currency conversions can distort the metric.

SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(total_amount)               AS revenue,
  COUNT(*)                        AS orders,
  AVG(total_amount)               AS avg_order_value
FROM orders
WHERE status = 'PAID'
  AND total_amount > 0           -- exclude zero or test orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Key points from this example of AVG:

• AVG ignores NULL, but not zero. You often need a filter to avoid skew.
• In data warehouses, AVG is usually computed as SUM/COUNT under the hood.
• For financial reporting, many teams prefer SUM(total_amount) / NULLIF(COUNT(*), 0) to keep the math explicit.

If you want a sanity check on how averages can mislead, the classic discussion of mean vs. median in statistics courses (for example, introductory materials from Harvard’s statistics resources) is still relevant to how you interpret AVG in SQL.

MIN and MAX for lifecycle analytics

MIN and MAX are the quiet workhorses. They show up in churn analysis, lifecycle tracking, and activity windows. Here’s one of the more practical examples of SQL aggregate functions for user lifecycle:

SELECT
  c.customer_id,
  MIN(o.order_date) AS first_order_date,
  MAX(o.order_date) AS last_order_date,
  COUNT(*)          AS order_count,
  SUM(o.total_amount) AS lifetime_value
FROM customers c
JOIN orders o USING (customer_id)
WHERE o.status = 'PAID'
GROUP BY c.customer_id;

This gives you customer-level aggregates you can feed into retention models or marketing segmentation.

You can then layer on logic to classify customers:

SELECT
  customer_id,
  first_order_date,
  last_order_date,
  order_count,
  lifetime_value,
  CASE
    WHEN last_order_date >= CURRENT_DATE - INTERVAL '30 days' THEN 'active'
    WHEN last_order_date >= CURRENT_DATE - INTERVAL '90 days' THEN 'at_risk'
    ELSE 'churned'
  END AS status_bucket
FROM (

  -- previous query here
) t;

This is a good example of how MIN, MAX, COUNT, and SUM combine to answer lifecycle questions in one pass.

Conditional aggregates: COUNT and SUM with CASE

Real data is messy. You rarely want just a raw COUNT(*); you want examples of conditional counts: paid vs. cancelled, mobile vs. desktop, domestic vs. international.

Here’s a single query that produces a compact status breakdown per month:

SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'PAID'      THEN 1 ELSE 0 END) AS paid_orders,
  SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END) AS cancelled_orders,
  SUM(CASE WHEN status = 'REFUNDED'  THEN 1 ELSE 0 END) AS refunded_orders
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

This pattern is one of the best examples of SQL aggregate functions used in dashboards:

• SUM over a CASE expression effectively gives you multiple filtered COUNTs in one scan.
• It’s often faster and easier to maintain than running three separate queries.

For anyone learning SQL, this is the example of conditional aggregation that unlocks a lot of reporting flexibility.

Percentages and ratios: aggregates inside derived metrics

Most business charts are ratios: conversion rates, cancellation rates, active user percentages. These are all examples of SQL aggregate functions wrapped in basic arithmetic.

Cancellation rate by month:

SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END) AS cancelled_orders,
  ROUND(
    100.0 * SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END)
          / NULLIF(COUNT(*), 0),
    2
  ) AS cancellation_rate_pct
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

A few things this example of aggregate math highlights:

• Always guard against division by zero with NULLIF.
• Use 100.0 (not 100) to force floating-point math in systems like PostgreSQL.
• ROUND is your friend if you don’t want six decimal places in your BI tool.

Ratios like this are the backbone of KPI dashboards, whether you’re tracking health data, ecommerce performance, or education outcomes. The logic is the same across domains, even when the source data comes from places like CDC public datasets or NIH research data.

Window functions vs. plain aggregates: running totals and moving averages

Sometimes you need both the aggregate and the raw rows. That’s where window functions come in. They’re not aggregates in the GROUP BY sense, but they reuse the same functions over a partition.

Running total revenue by day is a classic example of this pattern:

SELECT
  order_date,
  SUM(total_amount) AS daily_revenue,
  SUM(total_amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_revenue
FROM orders
WHERE status = 'PAID'
GROUP BY order_date
ORDER BY order_date;

Here, you see two examples of SQL aggregate functions side by side:

• A regular GROUP BY aggregate for daily_revenue.
• A windowed SUM that produces a running total without collapsing rows.

You can extend this to moving 7-day averages, which are everywhere in modern analytics (including COVID-19 dashboards from sources like CDC.gov):

SELECT
  order_date,
  SUM(total_amount) AS daily_revenue,
  AVG(SUM(total_amount)) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS revenue_7d_moving_avg
FROM orders
WHERE status = 'PAID'
GROUP BY order_date
ORDER BY order_date;

This is one of the best real examples of how aggregate functions evolve from simple totals into more sophisticated time-series tools.

Distinct counts: unique users, devices, and more

Distinct counts are deceptively simple. They’re also expensive at scale and often sampled or approximated in big warehouses.

Here’s a straightforward example of distinct-count aggregation:

SELECT
  DATE_TRUNC('day', order_date) AS day,
  COUNT(DISTINCT customer_id)   AS dau,   -- daily active users
  COUNT(*)                      AS total_orders
FROM orders
WHERE status = 'PAID'
GROUP BY DATE_TRUNC('day', order_date)
ORDER BY day;

In 2024–2025, this pattern is everywhere in product analytics. Behind the scenes, systems like BigQuery and Snowflake may use approximate algorithms (e.g., HyperLogLog) for large-scale distinct counts, especially when you see functions like APPROX_COUNT_DISTINCT.

This is a good reminder that even simple examples of SQL aggregate functions can behave differently depending on the engine and configuration.

Multi-level grouping: rollups and drilldowns

Another family of real examples involves grouping at multiple levels: by country, then by month, for instance.

SELECT
  c.country,
  DATE_TRUNC('month', o.order_date) AS month,
  COUNT(*)                          AS total_orders,
  SUM(o.total_amount)               AS revenue
FROM orders o
JOIN customers c USING (customer_id)
WHERE o.status = 'PAID'
GROUP BY c.country, DATE_TRUNC('month', o.order_date)
ORDER BY c.country, month;

You can layer on GROUPING SETS, ROLLUP, or CUBE in databases that support them to get multiple granularities in one query.

For example, a simple rollup by country and a grand total:

SELECT
  c.country,
  DATE_TRUNC('month', o.order_date) AS month,
  SUM(o.total_amount)               AS revenue
FROM orders o
JOIN customers c USING (customer_id)
WHERE o.status = 'PAID'
GROUP BY ROLLUP (c.country, DATE_TRUNC('month', o.order_date))
ORDER BY c.country NULLS LAST, month NULLS LAST;

This query gives you country+month, country total, and global total in one shot—another strong example of how aggregate functions scale up to multi-level reporting.

Putting it together: a realistic KPI summary query

To wrap the main section, here’s a single query that combines several of the best examples of SQL aggregate functions into one monthly KPI view:

SELECT
  DATE_TRUNC('month', o.order_date) AS month,
  COUNT(*) AS total_orders,
  COUNT(DISTINCT o.customer_id) AS unique_customers,
  SUM(CASE WHEN o.status = 'PAID'      THEN 1 ELSE 0 END) AS paid_orders,
  SUM(CASE WHEN o.status = 'CANCELLED' THEN 1 ELSE 0 END) AS cancelled_orders,
  SUM(o.total_amount) AS gross_revenue,
  AVG(o.total_amount) AS avg_order_value,
  ROUND(
    100.0 * SUM(CASE WHEN o.status = 'CANCELLED' THEN 1 ELSE 0 END)
          / NULLIF(COUNT(*), 0),
    2
  ) AS cancellation_rate_pct,
  MIN(o.total_amount) AS min_order_value,
  MAX(o.total_amount) AS max_order_value
FROM orders o
WHERE o.order_date >= DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year'
GROUP BY DATE_TRUNC('month', o.order_date)
ORDER BY month;

This is the kind of query you’d expect to sit behind a “last 12 months” dashboard tile in a BI tool. It pulls together COUNT, SUM, AVG, MIN, MAX, conditional aggregates, and ratio math—exactly the kind of examples of SQL aggregate functions people actually run in production.

FAQ: examples of SQL aggregate functions in practice

Q: What are some basic examples of SQL aggregate functions I should memorize first?
Start with COUNT, SUM, AVG, MIN, and MAX. For instance, COUNT(*) for total rows, SUM(total_amount) for revenue, AVG(score) for average rating, MIN(created_at) for first activity, and MAX(created_at) for most recent activity. These simple examples of aggregates cover a large portion of everyday reporting.

Q: Can you give an example of using multiple aggregate functions in one query?
Yes. A classic example of multiple aggregates is a customer summary:

SELECT
  customer_id,
  COUNT(*)          AS order_count,
  SUM(total_amount) AS total_spent,
  AVG(total_amount) AS avg_order_value,
  MIN(order_date)   AS first_order,
  MAX(order_date)   AS last_order
FROM orders
WHERE status = 'PAID'
GROUP BY customer_id;

Q: Are aggregate functions the same across all SQL databases?
The core functions—COUNT, SUM, AVG, MIN, MAX—behave very similarly across PostgreSQL, MySQL, SQL Server, and Oracle. Where things diverge is in advanced features like approximate distinct counts, window function syntax details, and extensions such as GROUPING SETS. Always check your specific engine’s docs; most major platforms provide detailed references, similar in spirit to how Harvard’s CS and data courses document their tooling.

Q: How do I avoid performance issues with aggregate-heavy queries?
Use selective filters, avoid unnecessary DISTINCTs, pre-aggregate data into summary tables when you can, and index join keys and filter columns. For truly large datasets, consider approximate aggregates (like APPROX_COUNT_DISTINCT) or columnar warehouses designed for analytics.

Q: What’s a good real example of aggregates in a non-business context?
Public health dashboards are full of them: daily case counts (SUM), 7-day moving averages (AVG over a window), highest daily hospitalization numbers (MAX), and first/last observation dates (MIN/MAX). Many of the COVID-19 visualizations on CDC.gov and research summaries from NIH.gov are backed by the same aggregate patterns you’d use on ecommerce or app data.