Aggregate Functions and GROUP BY: Summarizing Data With SQL

Raw tables hold events. Analysis needs summaries: revenue per region, average latency per hour, churned users per cohort. SQL collapses rows into those summaries with aggregate functions and the GROUP BY clause. Most dashboards, feature tables, and ETL rollups depend on this pair, and most silent reporting bugs come from misusing it.

Quick Takeaways

• Aggregate functions (COUNT, SUM, AVG, MIN, MAX) reduce many rows to one value. Except COUNT(*), they skip NULL.
• GROUP BY splits rows into buckets first. The aggregates then run once per bucket.
• WHERE filters rows before grouping. HAVING filters groups after aggregation.
• Every non-aggregated column in SELECT must appear in GROUP BY (or be functionally dependent on it).

How SQL Aggregates Data

An aggregate function takes a set of values and returns a single scalar. Without GROUP BY, the whole table is one group. With GROUP BY, each distinct key combination forms its own group.

Logical Order of Execution

SQL does not run clauses in the order you write them. The logical order explains most aggregation errors:

1. FROM / JOIN: assemble the rows
2. WHERE: drop rows
3. GROUP BY: form buckets
4. HAVING: drop buckets
5. SELECT: compute aggregates and expressions
6. ORDER BY, then LIMIT

WHERE runs before aggregates exist, so WHERE SUM(amount) > 400 is invalid. Use HAVING instead. Standard SQL also evaluates SELECT after GROUP BY, so some engines reject column aliases inside GROUP BY.

The Core Aggregate Functions

Setting Up a Sample Dataset

The script below uses Python’s built-in sqlite3 and pandas. It runs as-is, with no database server. Note the NULL amount in order 9, which exposes NULL behavior.

import sqlite3
import pandas as pd

conn = sqlite3.connect(":memory:")  # throwaway in-memory database

conn.executescript("""
CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    region      TEXT    NOT NULL,
    category    TEXT    NOT NULL,
    order_date  TEXT    NOT NULL,   -- ISO 8601 string
    amount      REAL,               -- nullable on purpose
    status      TEXT    NOT NULL
);

INSERT INTO orders VALUES
 (1, 101, 'West',  'Electronics', '2026-01-05', 250.00, 'completed'),
 (2, 102, 'West',  'Apparel',     '2026-01-09',  80.00, 'completed'),
 (3, 103, 'East',  'Electronics', '2026-01-12', 400.00, 'completed'),
 (4, 101, 'West',  'Electronics', '2026-02-03', 150.00, 'returned'),
 (5, 104, 'East',  'Apparel',     '2026-02-10',  60.00, 'completed'),
 (6, 105, 'South', 'Home',        '2026-02-14', 120.00, 'completed'),
 (7, 103, 'East',  'Home',        '2026-02-20',  90.00, 'completed'),
 (8, 106, 'South', 'Electronics', '2026-03-01', 300.00, 'completed'),
 (9, 102, 'West',  'Home',        '2026-03-07',   NULL, 'pending'),
 (10,107, 'East',  'Electronics', '2026-03-15', 500.00, 'completed');
""")

def run(sql):
    """Execute SQL and return a DataFrame."""
    return pd.read_sql_query(sql, conn)

Aggregate Functions Without GROUP BY

Run a table-wide summary first:

print(run("""
SELECT COUNT(*)      AS total_rows,
       COUNT(amount) AS non_null_amounts,
       SUM(amount)   AS total_amount,
       AVG(amount)   AS avg_amount,
       MIN(amount)   AS min_amount,
       MAX(amount)   AS max_amount
FROM orders;
"""))

COUNT(*) returns 10, but COUNT(amount) returns 9. AVG(amount) equals 1950 / 9, not 1950 / 10. If you want pending orders treated as zero, wrap the column: AVG(COALESCE(amount, 0)). That returns 195.0. State your NULL policy explicitly in every metric definition.

GROUP BY: Building Per-Bucket Summaries

Single-Column Grouping

print(run("""
SELECT region,
       COUNT(*)                AS orders,
       SUM(amount)             AS revenue,
       ROUND(AVG(amount), 2)   AS avg_order_value
FROM orders
WHERE status = 'completed'      -- row filter runs before grouping
GROUP BY region
ORDER BY revenue DESC;
"""))

Multi-Column Grouping

Each unique combination of keys becomes one bucket. Add columns to get finer granularity:

print(run("""
SELECT region, category, SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY region, category
ORDER BY region, revenue DESC;
"""))

Column order in GROUP BY does not change the groups. It only matters for the order you choose to present.

Grouping by Expressions

You can group by a computed value, such as a month bucket:

print(run("""
SELECT strftime('%Y-%m', order_date) AS order_month,   -- SQLite date function
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY strftime('%Y-%m', order_date)
ORDER BY order_month;
"""))

In PostgreSQL, use DATE_TRUNC('month', order_date). In MySQL, use DATE_FORMAT(order_date, '%Y-%m').

WHERE vs. HAVING

HAVING filters on aggregated results. Keep only regions whose completed revenue exceeds 400:

print(run("""
SELECT region, SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY region
HAVING SUM(amount) > 400      -- group-level filter
ORDER BY revenue DESC;
"""))

East (1050) and South (420) pass. West (330) is dropped.

Push every condition that does not need an aggregate into WHERE. The database then groups fewer rows.

Conditional Aggregation

Conditional aggregation computes several metrics in one pass over the table. The portable form uses CASE WHEN inside the aggregate:

print(run("""
SELECT region,
       COUNT(*) AS total_orders,
       SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returned_orders,
       ROUND(1.0 * SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END)
             / COUNT(*), 2) AS return_rate     -- 1.0 forces float division
FROM orders
GROUP BY region;
"""))

PostgreSQL and SQLite 3.30+ also support the cleaner FILTER clause: COUNT(*) FILTER (WHERE status = 'returned').

The 1.0 * prefix matters. Dividing two integers in SQLite, SQL Server, and MySQL’s DIV returns an integer in many cases, which silently turns your rate into 0.

Algorithm Comparison: GROUP BY vs. Window Functions vs. pandas

Use GROUP BY when you want a smaller table. Use a window function (SUM(amount) OVER (PARTITION BY region)) when each row needs its group’s total beside it.

The pandas equivalent of the region summary:

df = run("SELECT * FROM orders")

summary = (
    df[df["status"] == "completed"]            # WHERE
      .groupby("region", as_index=False)       # GROUP BY
      .agg(orders=("order_id", "count"),       # COUNT(*)
           revenue=("amount", "sum"),          # SUM(amount)
           avg_order_value=("amount", "mean")) # AVG(amount)
      .query("revenue > 400")                  # HAVING
      .sort_values("revenue", ascending=False) # ORDER BY
)
print(summary)

Pandas count and mean also skip NaN, which mirrors SQL’s NULL rules.

Advanced Grouping: ROLLUP, CUBE, and GROUPING SETS

Reports often need subtotals and a grand total. Running separate queries and gluing them with UNION ALL is slow and error-prone. PostgreSQL, SQL Server, and Oracle offer these extensions (MySQL has WITH ROLLUP; SQLite has none):

-- PostgreSQL: revenue by region and category, with subtotals and a grand total
SELECT region,
       category,
       SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY ROLLUP (region, category)   -- adds per-region subtotals + grand total
ORDER BY region NULLS LAST, category NULLS LAST;

Subtotal rows show NULL in the rolled-up columns. Use GROUPING(region) to tell a subtotal NULL from a genuine NULL in the data.

Common Pitfalls

Selecting non-grouped columns. SELECT region, category, SUM(amount) ... GROUP BY region fails in PostgreSQL and in MySQL with ONLY_FULL_GROUP_BY (the default since 5.7). Add category to GROUP BY or aggregate it.

Join fan-out. Joining orders to a one-to-many table duplicates order rows. SUM(amount) then overcounts. Aggregate the many-side in a subquery or CTE first, then join.

Treating NULL as a group. GROUP BY places all NULL keys into a single bucket. Clean or label them with COALESCE(region, 'Unknown').

Averaging averages. The mean of regional means is not the global mean unless groups are equal in size. Carry SUM and COUNT forward and divide at the end.

Distinct counts on huge tables. COUNT(DISTINCT user_id) is expensive. Warehouses like BigQuery and Snowflake offer approximate versions (APPROX_COUNT_DISTINCT) with small error and large speedups.

Real-World Use Cases

Use Case 1: E-Commerce Revenue Reporting

Finance wants monthly revenue by category, excluding returns and bounded to the current year. The pattern is WHERE (status and date), GROUP BY (month, category), and ORDER BY. Add HAVING SUM(amount) > 0 to drop empty buckets.

Use Case 2: Calculating Monthly Churn in SaaS

Churn is a ratio of two conditional counts. Compute both in one pass:

-- PostgreSQL: monthly churn rate
SELECT DATE_TRUNC('month', event_date)                     AS month,
       COUNT(DISTINCT user_id) FILTER (WHERE event = 'cancelled') AS churned_users,
       COUNT(DISTINCT user_id)                              AS active_users,
       ROUND(100.0 * COUNT(DISTINCT user_id) FILTER (WHERE event = 'cancelled')
             / NULLIF(COUNT(DISTINCT user_id), 0), 2)       AS churn_pct   -- NULLIF avoids divide-by-zero
FROM subscription_events
GROUP BY 1
ORDER BY 1;

Use Case 3: Hourly Sensor Rollups

IoT pipelines store millions of readings. Downstream models want hourly statistics:

-- PostgreSQL: hourly stats per device
SELECT device_id,
       DATE_TRUNC('hour', reading_ts) AS hour,
       AVG(temperature)               AS avg_temp,
       MIN(temperature)               AS min_temp,
       MAX(temperature)               AS max_temp,
       STDDEV(temperature)            AS temp_stddev,
       COUNT(*)                       AS readings
FROM sensor_readings
WHERE temperature IS NOT NULL
GROUP BY device_id, DATE_TRUNC('hour', reading_ts);

Compare readings against the expected sampling rate to spot dropped data. A bucket with 12 readings instead of 3,600 signals a gateway outage, not a cold hour.

Performance Tips for Large Tables

• Filter early with WHERE and index the filter columns.
• Group on low-cardinality or indexed keys when possible. Databases can use sorted index order to avoid a separate sort or hash step.
• Pre-aggregate into summary tables or materialized views for dashboards that run the same rollup repeatedly.
• Check EXPLAIN ANALYZE for HashAggregate versus GroupAggregate nodes, and watch for spills to disk.
• Select only the columns you need. Wide rows slow hash aggregation.

FAQ

What is the difference between WHERE and HAVING in SQL?

WHERE filters individual rows before grouping and cannot reference aggregate functions. HAVING filters groups after aggregation and can use functions like SUM() or COUNT(). Use WHERE for row conditions and HAVING only for conditions on aggregated values.

Can you use GROUP BY without an aggregate function?

Yes. SELECT region FROM orders GROUP BY region returns each distinct region once, which matches SELECT DISTINCT region. It is rarely useful on its own. GROUP BY pays off when paired with COUNT, SUM, or other aggregates.

Why does COUNT(*) return a different number than COUNT(column)?

COUNT(*) counts every row. COUNT(column) counts only rows where that column is not NULL. In the sample data, COUNT(*) is 10 and COUNT(amount) is 9 because one order has a NULL amount.

Can you GROUP BY a column alias?

It depends on the engine. PostgreSQL, MySQL, and SQLite accept output aliases in GROUP BY. SQL Server does not, so repeat the full expression or wrap the query in a subquery or CTE. Repeating the expression is the portable choice.