GROUP BY Done Right

The total looks plausible until somebody checks one customer. GROUP BY works well when you decide what a row means before calculating the total.

Name the Grain Before Grouping

If an Orders table has one row per order, grouping by CustomerId returns one row per customer. If a join adds order lines, the input grain changes. Summing Orders.Amount after that join can count an order once for every line. The syntax is valid, but the business total is wrong.

I write the desired output grain in a comment above complex aggregates. That short note forces a choice. One row per customer and month needs both keys in GROUP BY. A column selected without aggregation or grouping has no single value at that grain.

Ask what each input row represents. This is the most useful question in an aggregate review. A correct aggregate on the wrong input rows still produces the wrong answer.

SELECT CustomerId, COUNT_BIG(*) AS OrderCount,
       SUM(OrderAmount) AS TotalAmount
FROM dbo.Orders
GROUP BY CustomerId;

Filter Rows With WHERE Before GROUP BY

WHERE removes input rows before groups are formed. Use it for date ranges, statuses, and source flags that decide which facts participate. A half open date range is safer for datetime values than converting the column to date. It includes all times before the next boundary.

I keep the filter aligned with the metric name. A total called Paid Revenue should filter paid orders before SUM. If the report also needs all-order count, use conditional aggregation or separate queries with clear names. One hidden WHERE clause can change the meaning of every aggregate.

A selective WHERE predicate can also reduce work before grouping. Check the plan and indexes on your workload, but settle the business filter first. SQL Server should not optimize the wrong set with great enthusiasm.

SELECT CustomerId, SUM(OrderAmount) AS PaidAmount
FROM dbo.Orders
WHERE OrderStatus = 'Paid'
  AND OrderDate >= '2025-01-01'
  AND OrderDate < '2025-02-01'
GROUP BY CustomerId;

Filter GROUP BY Results With HAVING

HAVING tests an aggregate after groups exist. Use it for requirements such as customers with more than one order or months with a total above a threshold. Do not move a row-level condition into HAVING simply because both places can accept some expressions. The stages have different meanings.

An aggregate alias is not normally available to HAVING in the same SELECT scope. Repeat the expression or put the aggregate in a CTE and filter in an outer query. The second option is helpful when the calculation is complex and needs a meaningful name.

I test the boundary value. If the requirement says at least two orders, use greater than or equal to two. A small operator mistake can change an alert population without producing any SQL error.

SELECT CustomerId, COUNT_BIG(*) AS OrderCount
FROM dbo.Orders
GROUP BY CustomerId
HAVING COUNT_BIG(*) >= 2;

Understand the NULL Group

GROUP BY places NULL values into one group. That can be useful when NULL means unknown, but it can also hide a data quality problem inside a single row labeled blank. Decide whether the report should show Unknown, reject those rows, or exclude them under an explicit rule.

COUNT(*) includes rows with NULL values. COUNT(ColumnName) excludes rows where that column is NULL. The difference is not a quirk. It answers two different questions. Choose deliberately when calculating completeness or averages.

I show the NULL row during validation even if the final report omits it. If it suddenly grows, the source or load needs attention. An aggregate that silently filters NULL can make a broken feed look like a normal decline.

SELECT RegionCode, COUNT_BIG(*) AS AllRows,
       COUNT(RegionCode) AS KnownRegionRows
FROM dbo.Orders
GROUP BY RegionCode;

Avoid Totals Multiplied by Joins

A common mistake is joining Orders to OrderLine and then summing Orders.OrderAmount. Each order amount repeats once per line. Aggregate the line amount at the intended grain, or aggregate orders before the join. Look at key uniqueness on both sides of every join.

I use a quick duplicate check on the supposed one-side table. If Customer has two rows for one CustomerId, a join to it can double facts. The aggregate at the end cannot repair that multiplication. It only hides the cause inside a larger number.

When a report joins several dimensions, keep their join keys and effective dates explicit. Slowly changing dimensions need a date-aware lookup. The query should produce one dimension row per fact before it reaches SUM.

SELECT OrderId, COUNT_BIG(*) AS LineCount
FROM dbo.OrderLine
GROUP BY OrderId
HAVING COUNT_BIG(*) > 1;

Keep Types and Rounding Visible

SUM returns a result type based on its input expression. Check decimal precision when adding many amounts, and cast deliberately if the source type is too narrow. Round at the business-approved stage. Rounding each line before summing can differ from rounding the final total.

AVG ignores NULL values and uses the non-NULL count. If missing amounts should be treated as zero, say so and use COALESCE under that rule. Do not let a convenience expression decide policy by accident. The denominator belongs in the definition of the measure.

I compare one sample key’s detailed rows with its aggregate. A total without a trace to source rows is hard to defend. The query can show both when developing, then the report can use the concise version.

Check the Answer Against the Requirement

Validate a key with one row, one with several, one with NULL, and one with rows outside the date filter. Those cases catch most logic errors. Compare the grand total of grouped results with a direct base-table total under the same row filter.

The two totals should reconcile when groups are exhaustive and nonoverlapping. If they do not, inspect joins and filters before tuning indexes. I want the arithmetic settled before touching the execution plan. A fast aggregate with an unexplained difference is still a support call waiting to happen.

GROUP BY is simple syntax with a deep question behind it: what set of rows belongs to each answer row? State that answer in plain words, then make WHERE and HAVING reflect the correct stages.

When totals look wrong, inspect the grouping key before changing the aggregate. Joining an order header to several detail rows multiplies header values, and grouping afterward cannot always restore the intended meaning. Aggregate detail rows at their own grain first, then join that result to headers. Check one order with multiple details and one with none. The output should make the expected grain obvious.

Related reading on this blog: What is Difference Between HAVING and WHERE: Interview Question of the Week #068 and Warning: Null value is Eliminated by an Aggregate or Other SET Operation.

GROUP BY is not a repair for bad joins, it is a way to state the grain of an answer.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.