SQL GROUP BY — 40% Revenue Inflation from Missing Column

You’ve written GROUP BY a hundred times. It feels straightforward—collapse rows, sum totals, get answers. The trouble starts when you need to filter those aggregated results, and that’s where HAVING comes in. Without it, you either miss critical filters or accidentally drop rows before they ever get summed. This article unpacks exactly how HAVING works, where it fits in SQL execution, and the common traps that turn clean queries into silent bugs.

Why GROUP BY Without HAVING Is Only Half the Story

GROUP BY collapses rows into groups based on column equality, then HAVING filters those groups after aggregation. The core mechanic: GROUP BY partitions the result set, applies aggregate functions (SUM, COUNT, AVG) per partition, and HAVING acts as a WHERE clause for groups — evaluated after aggregation, not before. Without HAVING, you get all groups; with it, you keep only groups satisfying a condition on the aggregate.

In practice, GROUP BY groups on every column in the SELECT list that isn't wrapped in an aggregate. A common mistake: omitting a column from GROUP BY that appears in SELECT — SQL silently picks an arbitrary value from that column per group, inflating revenue by up to 40% in production. HAVING filters at the group level, so conditions like SUM(amount) > 1000 run after aggregation, not on individual rows. This distinction matters: WHERE filters rows before grouping, HAVING filters groups after.

Use GROUP BY + HAVING when you need to answer questions like "which customers spent more than $10k total?" or "which products had fewer than 5 returns?" — any query where the filter depends on the aggregate result. In real systems, this pattern is essential for reporting, anomaly detection, and data quality checks. Without HAVING, you'd either over-report or have to subquery, which is slower and harder to read.

GROUP BY: Collapsing Rows Into Meaningful Summaries

GROUP BY tells the database to treat all rows that share the same value in a column as a single unit, then apply an aggregate function — SUM, COUNT, AVG, MAX, MIN — across that unit. The result set has one row per unique group, not one row per original record.

This is critical to internalise: after a GROUP BY, every column in your SELECT must either be the grouped column itself or wrapped in an aggregate function. If you select a non-grouped, non-aggregated column the database doesn't know which of the many original rows' values to display. Some databases (MySQL with loose mode) will silently pick a random row's value. PostgreSQL and SQL Server will flat-out error. Neither outcome is what you want.

The mental model that helps most: picture the database first sorting all rows by the GROUP BY column, then drawing a horizontal line between each new value, then running your aggregate function on each block between the lines. You only ever see the aggregated result per block — the individual rows are gone from view.

-- Sample table: orders
-- Columns: order_id, customer_id, category, amount, order_date

-- ── BASIC GROUP BY ──────────────────────────────────────────────
-- Goal: find total revenue and number of orders per product category

SELECT
    category,                        -- the grouping column — one row per unique value
    COUNT(order_id)   AS order_count, -- how many orders fell into this group
    SUM(amount)       AS total_revenue,-- aggregate: sum of all amounts in the group
    ROUND(AVG(amount), 2) AS avg_order_value -- aggregate: mean order size
FROM orders
GROUP BY category                    -- collapse every category into one summary row
ORDER BY total_revenue DESC;         -- show highest-revenue category first

-- ── GROUPING BY MULTIPLE COLUMNS ────────────────────────────────
-- Goal: break revenue down by category AND by the year of the order
-- This gives one row per (category, year) combination

SELECT
    category,
    EXTRACT(YEAR FROM order_date) AS order_year, -- derive year from the date column
    COUNT(order_id)               AS order_count,
    SUM(amount)                   AS total_revenue
FROM orders
GROUP BY
    category,               -- group first by category...
    EXTRACT(YEAR FROM order_date) -- ...then by year within each category
ORDER BY order_year, total_revenue DESC;

HAVING: Filtering Groups After Aggregation (Not Before)

Here's the question that unlocks HAVING: once you've run GROUP BY and computed SUM(amount) per category, how do you show only categories where that total exceeds $50,000? You can't use WHERE — by the time WHERE runs, the aggregation hasn't happened yet. WHERE is a row-level filter applied to the raw table. HAVING is a group-level filter applied to the aggregated output.

Think of the SQL execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE filters raw rows before they enter the grouping step. HAVING filters the grouped rows after aggregation is complete. This is why you can reference SUM(amount) in HAVING but not in WHERE.

HAVING is also where you enforce data quality thresholds. 'Show me customers, but only if they've placed at least 5 orders' is a HAVING condition — COUNT(order_id) >= 5. It's common to combine both WHERE and HAVING in one query: WHERE removes irrelevant raw rows early (which improves performance by feeding fewer rows into the grouping step), and HAVING then prunes the resulting groups.

-- ── HAVING: filter groups by their aggregate value ──────────────
-- Goal: find product categories that have generated over $50,000
-- and had more than 100 individual orders

SELECT
    category,
    COUNT(order_id)  AS order_count,
    SUM(amount)      AS total_revenue
FROM orders
GROUP BY category
HAVING
    SUM(amount)     > 50000   -- only keep groups where the SUM clears this bar
    AND COUNT(order_id) > 100 -- AND the group contains more than 100 orders
ORDER BY total_revenue DESC;


-- ── WHERE + GROUP BY + HAVING together ──────────────────────────
-- Goal: same report, but exclude any orders placed before 2023
-- (WHERE trims raw rows BEFORE grouping — faster than filtering in HAVING)

SELECT
    category,
    COUNT(order_id)  AS order_count,
    SUM(amount)      AS total_revenue
FROM orders
WHERE order_date >= '2023-01-01'  -- row-level filter: remove old orders FIRST
GROUP BY category
HAVING
    SUM(amount)     > 50000        -- group-level filter: applied AFTER aggregation
    AND COUNT(order_id) > 100
ORDER BY total_revenue DESC;


-- ── HAVING without GROUP BY (edge case worth knowing) ────────────
-- HAVING can technically apply to the entire table as one group.
-- This returns the row only if the whole orders table has > 1000 rows.
-- Rarely useful, but interviewers love asking if it's valid SQL.

SELECT COUNT(*) AS total_orders
FROM orders
HAVING COUNT(*) > 1000;

Real-World Patterns: How GROUP BY and HAVING Are Actually Used

In production code these clauses rarely appear in isolation. Here are three patterns you'll encounter constantly and should be able to write from memory.

The first is the top-N per group pattern: find the most active customers, highest-revenue regions, or most common error codes. You GROUP BY the entity, aggregate a metric, HAVING optionally filters noise, then ORDER BY the metric with LIMIT to get your top results.

The second is the data quality audit pattern: find anomalies by grouping records that should be unique and counting how many duplicates exist. A COUNT(*) > 1 in HAVING is the classic duplicate-detection query every data engineer writes at some point.

The third is the cohort threshold pattern common in analytics: 'show me all users who took action X at least N times.' This is GROUP BY user_id, HAVING COUNT(*) >= N — the backbone of retention and engagement reports. Once you recognise these shapes, you stop treating GROUP BY and HAVING as separate features and start seeing them as a single analytical toolset.

-- ── PATTERN 1: Top-N customers by total spend ───────────────────
-- Goal: find the 5 highest-spending customers in 2024

SELECT
    customer_id,
    COUNT(order_id)  AS total_orders,
    SUM(amount)      AS lifetime_spend
FROM orders
WHERE order_date >= '2024-01-01'   -- narrow to 2024 first (WHERE = pre-filter)
GROUP BY customer_id
HAVING SUM(amount) > 0             -- exclude any customer with $0 net (edge case)
ORDER BY lifetime_spend DESC
LIMIT 5;                           -- take only the top 5 after sorting


-- ── PATTERN 2: Duplicate detection ──────────────────────────────
-- Goal: find any email addresses that appear more than once in the
-- customers table — a sign of duplicate registrations

SELECT
    email,
    COUNT(*) AS registration_count   -- count how many rows share this email
FROM customers
GROUP BY email
HAVING COUNT(*) > 1                  -- HAVING filters to only the duplicates
ORDER BY registration_count DESC;    -- worst offenders first


-- ── PATTERN 3: Engagement cohort — users who placed 3+ orders ───
-- Goal: identify loyal repeat customers for a marketing campaign

SELECT
    customer_id,
    COUNT(order_id)      AS order_count,
    MIN(order_date)      AS first_order_date,  -- when they became a customer
    MAX(order_date)      AS latest_order_date, -- most recent activity
    SUM(amount)          AS total_spend
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) >= 3          -- only customers with 3 or more orders
ORDER BY total_spend DESC;

Common Mistakes That Silently Break Your Queries

Most GROUP BY and HAVING bugs don't throw errors — they return wrong results that look plausible, which makes them especially dangerous. These are the ones that catch experienced developers off guard, not just beginners.

The first is using WHERE where you need HAVING, or vice versa. It sounds obvious once you know the execution order, but under deadline pressure it's easy to write WHERE SUM(amount) > 50000 and then be confused when the database throws an error about aggregate functions not being allowed in WHERE.

The second is including a column in SELECT that isn't in GROUP BY and isn't aggregated — discussed earlier, but worth re-emphasising because MySQL's lenient default mode will run the query and silently return a non-deterministic value from one of the grouped rows.

The third is a subtler one: filtering on a column alias in HAVING. Because HAVING runs after SELECT in the logical execution order (but before the alias is fully materialised in most engines), referencing an alias defined in SELECT often fails. You need to repeat the expression.

-- ── MISTAKE 1: Using WHERE on an aggregate ───────────────────────
-- WRONG — this throws: "aggregate functions are not allowed in WHERE"
SELECT category, SUM(amount) AS total_revenue
FROM orders
WHERE SUM(amount) > 50000   -- ❌ WHERE runs before aggregation — SUM doesn't exist yet
GROUP BY category;

-- FIXED — move the aggregate condition to HAVING
SELECT category, SUM(amount) AS total_revenue
FROM orders
GROUP BY category
HAVING SUM(amount) > 50000;  -- ✅ HAVING runs after aggregation — SUM exists here


-- ── MISTAKE 2: Referencing a SELECT alias in HAVING ──────────────
-- WRONG — most databases can't resolve the alias 'total_revenue' in HAVING
-- (PostgreSQL and SQL Server will error; MySQL may accept it as a non-standard extension)
SELECT
    category,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY category
HAVING total_revenue > 50000;  -- ❌ alias not guaranteed to be visible in HAVING

-- FIXED — repeat the full aggregate expression in HAVING
SELECT
    category,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY category
HAVING SUM(amount) > 50000;   -- ✅ reference the expression, not the alias


-- ── MISTAKE 3: Forgetting that COUNT(*) includes NULLs ───────────
-- COUNT(*) counts all rows in the group, including rows with NULL values.
-- COUNT(column_name) counts only non-NULL values in that column.
-- These can give different numbers — and the difference matters.

SELECT
    category,
    COUNT(*)          AS all_rows,        -- counts every row, NULLs included
    COUNT(amount)     AS non_null_amounts -- counts only rows where amount IS NOT NULL
FROM orders
GROUP BY category;

-- If your 'amount' column has NULLs, these two columns will differ.
-- Use COUNT(column_name) when you specifically want to exclude NULLs from your count.

Window Functions vs. GROUP BY: When HAVING Steals Your Shine

Junior devs treat GROUP BY like the only hammer for summary work. That's wrong. HAVING filters groups, yes, but it also destroys row-level context. If you need a summary alongside original data, HAVING forces you into two queries or a messy self-join. Window functions keep the rows and let you filter on aggregate logic without collapsing the set. Why does this matter? Production dashboards and audit logs routinely need things like 'show all orders from customers whose lifetime spend is over $10k'. With GROUP BY + HAVING you lose the individual order rows. With a windowed SUM + QUALIFY or WHERE clause, you don't. Understand the cost: HAVING trades data resolution for simplicity. That's fine for canned reports. Dangerous for debugging. I've seen teams spend days rebuilding query logic because a HAVING clause erased the transaction detail they needed for an outage postmortem. Pick the tool for the job. If you need both worlds — group-level filters and row-level detail — window functions are your escape hatch.

// io.thecodeforge — database tutorial

-- Window function: keeps rows, filters on aggregate
SELECT
    order_id,
    customer_id,
    order_amount,
    SUM(order_amount) OVER (PARTITION BY customer_id) AS customer_lifetime_value
FROM orders
QUALIFY SUM(order_amount) OVER (PARTITION BY customer_id) > 10000
ORDER BY customer_id;

-- GROUP BY + HAVING equivalent: loses all row-level details
SELECT
    customer_id,
    SUM(order_amount) AS customer_lifetime_value
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) > 10000;

Order of Execution: Why HAVING Can't See Your Aliases

You wrote a beautiful GROUP BY query, aliased your aggregate, then used that alias in HAVING. It threw an error. You cursed the database. Here's the fix: SQL's logical order of execution runs HAVING before SELECT. That means HAVING only sees the original column names, not your fresh alias. This trips up everyone at least once in production. I've debugged three separate incidents where engineers wrote HAVING total_revenue > 5000 and wondered why the parser refused, then slapped the aggregate expression back in and moved on without understanding why. The WHY is the execution order: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT. HAVING evaluates on grouped rows before SELECT assigns its projection aliases. The HOW is simple: duplicate the full aggregate expression in HAVING, or use a subquery/CTE. The duplicate isn't elegant, but it's explicit. The subquery is cleaner for complex cases. Pick one, document why, and move on. This isn't a deficiency — it's a deliberate design choice that enforces discipline at the expense of convenience.

// io.thecodeforge — database tutorial

-- BAD: alias not visible in HAVING
SELECT
    customer_id,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY customer_id
HAVING total_revenue > 5000;  -- ERROR: column 'total_revenue' does not exist

-- GOOD: repeat aggregate expression
SELECT
    customer_id,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 5000;

-- CLEANER: CTE for readability
WITH customer_revenue AS (
    SELECT
        customer_id,
        SUM(amount) AS total_revenue
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM customer_revenue
WHERE total_revenue > 5000;

GROUP BY Beyond Aggregates: Using HAVING for Data Quality Checks

Most devs see HAVING only as a revenue filter. That's narrow. HAVING is your first line of defense for data quality in pipelines. Think about it: you can detect orphan records, null heaps, and inconsistent cardinalities with a single GROUP BY + HAVING pass before any business logic touches the data. Why should care? Because dirty data that hits a dashboard or training pipeline costs hours of retroactive cleanup — or worse, wrong decisions. I've used HAVING COUNT(*) = 1 to find duplicate primary keys. HAVING MIN(date) = MAX(date) to spot no-change batches. HAVING COUNT(DISTINCT status) > 1 to catch row-level state corruption. This pattern runs fast on indexed GROUP BY columns and catches anomalies at ingestion time. It's cheap insurance. The HOW: wrap your raw table in a GROUP BY on the suspect column, then HAVING on a diagnostic aggregate. No joins, no subqueries, just raw detection. Attach it as a pre-step in your ETL or scheduled data health check. I've caught staging tables with null foreign keys in production on day one using exactly this. Your data is only as good as your willingness to distrust it.

// io.thecodeforge — database tutorial

-- Detect duplicate order IDs (should never happen)
SELECT
    order_id,
    COUNT(*) AS row_count
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

-- Detect orders where all timestamps are identical (stale batch load)
SELECT
    batch_id,
    MIN(loaded_at) AS first_loaded,
    MAX(loaded_at) AS last_loaded,
    COUNT(*) AS rows
FROM orders
GROUP BY batch_id
HAVING MIN(loaded_at) = MAX(loaded_at);

-- Find customers with multiple active statuses (data corruption)
SELECT
    customer_id,
    COUNT(DISTINCT status) AS status_variants
FROM customers
GROUP BY customer_id
HAVING COUNT(DISTINCT status) > 1;

Why COUNT(*) and COUNT(column) Are Not the Same Thing

I still see devs treat COUNT(*) and COUNT(column) like interchangeable aliases. They're not. And the difference will bite you in production.

COUNT(*) counts every row in the group — including rows where every column is NULL. It's the total row count, period. COUNT(column) only counts non-NULL values in that specific column. If your column has NULLs, you're silently dropping data from your aggregation.

In a GROUP BY with HAVING, this matters. Say you're counting orders per customer. COUNT(order_id) and COUNT() give the same result only if order_id is NOT NULL. If a row exists but the order_id is NULL (maybe a failed transaction), COUNT() includes it; COUNT(order_id) doesn't. Your HAVING filter now depends on which COUNT you chose.

Always ask: do I want the count of rows that exist, or the count of rows with a meaningful value? Pick the right COUNT before it picks your database apart.

// io.thecodeforge — database tutorial

SELECT
    customer_id,
    COUNT(*) AS total_rows,
    COUNT(order_id) AS orders_with_id,
    COUNT(DISTINCT order_id) AS unique_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > COUNT(order_id);

-- Output shows customers with NULL order_ids

Mastering GROUP BY: The Two Examples You'll Use Every Week

Every production query I write falls into one of two patterns: aggregate by a dimension, then filter groups. Here are the two examples that cover 80% of use cases.

First: total quantity sold per product. Straightforward GROUP BY on product_id, SUM(quantity). Need to see only products that moved? Add HAVING. This is your bread-and-butter for inventory dashboards and sales reports.

Second: countries with revenue over $2000. Same pattern — GROUP BY country, SUM(revenue), then HAVING SUM(revenue) > 2000. The WHERE clause filters rows before aggregation (e.g., only completed orders). HAVING filters after aggregation (only countries that hit the revenue bar). This distinction is why your queries run fast instead of crawling.

Both examples look simple. That's the point. The complexity people add with subqueries and CTEs is usually just poorly placed filters. Get GROUP BY and HAVING right at this basic level, and you'll solve most aggregation problems before they become incidents.

// io.thecodeforge — database tutorial

-- Example 1: Sales quantity per product
SELECT product_id, SUM(quantity) AS total_sold
FROM sales
WHERE status = 'completed'
GROUP BY product_id
HAVING SUM(quantity) > 100;

-- Output:
product_id | total_sold
P100       | 450
P203       | 312

-- Example 2: Countries with revenue > $2000
SELECT country, SUM(amount) AS total_revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY country
HAVING SUM(amount) > 2000;

-- Output:
country    | total_revenue
US         | 12500.00
DE         | 3400.50