SQL Server
GROUP BY Explained in SQL Server
Learn how GROUP BY turns SQL Server rows into one summary row per status, customer, or combination of values.
On this page
In the previous lesson, COUNT and SUM summarized an entire input set as one row. But questions often ask for more detail: how many orders for each status, or what each customer has spent? Aggregate functions answer “what is the total?” GROUP BY answers “what is the total for each group?” It changes the result grain: the meaning of one output row.
One result row per status
The demo has eight orders across four statuses: Complete, Pending, Shipped, and Processing. Group the rows by Status and count each group:
SELECT
Status,
COUNT(*) AS OrderCount
FROM dbo.Orders
GROUP BY Status;SQL Server puts rows with the same Status value in a group, then returns one result row per distinct status. Complete has 4 orders, Pending has 2, Shipped has 1, and Processing has 1. The output has four rows, not eight: each row represents one status group. Unless you add ORDER BY, SQL Server does not promise a particular result order.
- Orders: 8 rows
- GROUP BY Status
- Complete: 4
- Pending: 2
- Shipped: 1
- Processing: 1
Calculate an aggregate for each group
GROUP BY is commonly used with aggregate functions. This query calculates total order value for every status:
SELECT
Status,
SUM(Amount) AS TotalSales
FROM dbo.Orders
GROUP BY Status;The known totals are Complete 449.49, Pending 335.00, Shipped 82.75, and Processing NULL because its only Amount is unknown. The function runs separately for each group. COUNT(*) counts the rows in each status group, while SUM(Amount) adds that group’s amounts. These values come from the eight rows in dbo.Orders; SUM ignores the NULL Amount in the Processing group.
You can group by customer to get one row for each customer who has at least one order:
SELECT
CustomerId,
COUNT(*) AS OrderCount,
SUM(Amount) AS Amount
FROM dbo.Orders
GROUP BY CustomerId;This produces four rows for CustomerId 1, 2, 4, and 7. Ana (1) has 3 orders totaling 250.49; Ben (2) has 1 totaling 310.00; David (4) has 2 totaling 281.75; Grace (7) has 2 orders; their known amounts total 25.00. Customers without orders do not appear because there are no order rows to place in their groups.
Include customers without orders
To report every customer, even one without a matching order, start from Customers and use a LEFT JOIN:
SELECT
c.CustomerId,
c.FirstName,
c.LastName,
COUNT(o.OrderId) AS OrderCount,
SUM(o.Amount) AS Amount
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
ON c.CustomerId = o.CustomerId
GROUP BY
c.CustomerId,
c.FirstName,
c.LastName;The customer columns are in GROUP BY because they identify the row we want to return for each customer. COUNT(o.OrderId) counts matched orders and returns zero for customers without one: their joined OrderId is NULL, and COUNT(column) ignores NULL. SUM(o.Amount) is NULL for a customer with no orders because there are no non-NULL amounts to add. If a report should display zero instead, COALESCE(SUM(o.Amount), 0) replaces that NULL result with zero. COALESCE returns the first non-NULL expression; it changes the displayed result, not the source data.
The rule of thumb: if a selected expression is not aggregated, it generally belongs in the GROUP BY list. SQL Server needs to know which value to show for each group. Selecting a customer name while grouping only by Status would be ambiguous because a status group may contain several customers. Group by the columns that define the intended summary row.
Choose the grain before the columns
Before writing a grouped query, say what one output row should represent. This is the result grain, a concept from the lesson on repeated join rows:
- one row per status:
GROUP BY Status; - one row per customer:
GROUP BY CustomerId(and any displayed customer attributes); - one row per customer and status:
GROUP BY CustomerId, Status.
For example, grouping on two columns creates groups for their combinations:
SELECT
Status,
CustomerId,
COUNT(*) AS OrderCount
FROM dbo.Orders
GROUP BY
Status,
CustomerId;Now each result row means one particular customer-status combination. If one customer has orders in two statuses, that customer can appear in two rows. Adding a grouping column makes the grain finer and usually increases the number of groups. Grouping by too few columns can combine facts you meant to keep separate; grouping by too many can split a useful summary into many small rows.
GROUP BY, DISTINCT, and ordering
DISTINCT removes duplicate combinations from the selected output columns. GROUP BY forms groups and is typically used to calculate aggregates for them. A query selecting only Status with DISTINCT can list the statuses; a grouped query with COUNT(*) can also report how many orders each status has. They overlap for some simple outputs, but they are not interchangeable for every query because grouping gives aggregate calculations a defined group.
To order a summary, add ORDER BY:
SELECT
Status,
SUM(Amount) AS Amount
FROM dbo.Orders
GROUP BY Status
ORDER BY Amount DESC;This sorts the three status totals from largest to smallest. ORDER BY affects presentation order, not which rows belong to each group.
The same reasoning applies when the grouped result becomes input to a chart or export. A status chart needs one row per status; a customer ranking needs one row per customer and a measure to sort. If the query returns order detail instead, a chart may count repeated names or amounts in an unintended way. Check both the row count and the meaning of the grouping key before presenting a summary to someone who will not see the SQL.
How to reason through a grouped query
It helps to work from the question instead of starting with a list of columns. First state the grain: for example, one row per customer. Next identify the key that defines that grain, such as CustomerId. Then decide which facts you want to calculate for each key: order count, total amount, or both. Finally, decide whether the input needs a filter or whether unmatched customers must remain. This sequence reduces the chance of adding columns that silently change what each result row means.
For a status summary, Status is the grouping key and each row is one status. For customer totals, CustomerId is the key. A customer’s name is descriptive information for that key; because SQL Server needs one well-defined value per group, include the name columns in the grouping when selecting them. CustomerId is unique in the demo, so FirstName and LastName identify the same customer in each group. In other situations, do not assume a displayed column is functionally tied to a key unless the data model guarantees it.
You can check a grouped result against the source. The counts by status should add up to all eight orders: 4 + 2 + 1 + 1 = 8. The totals should add up to 867.24: 449.49 + 335.00 + 82.75 = 867.24. This is a useful sanity check for this simple dataset. If the totals do not reconcile, inspect your WHERE conditions, join relationships, and grouping columns. A correct-looking number for each group does not prove the query includes the intended rows.
A LEFT JOIN changes what is available for grouping. It preserves every customer and supplies NULLs for order columns when there is no match. COUNT(o.OrderId) counts only actual order IDs, while COUNT(*) counts the joined customer row too. For a customer without an order, those return different answers: zero versus one. SUM over the missing order side is NULL because no non-NULL amount was provided. Use COALESCE only if a display value of zero is appropriate. The choice is about the meaning of the report: “no recorded total” and “a known total of zero” are not always the same statement.
Grouping columns should also match the question’s level of detail. If you add OrderDate to a customer summary, each distinct date creates a separate customer-date group, so the output is no longer one row per customer. Adding FirstName is generally redundant when CustomerId uniquely determines the customer, but it is needed in the SELECT list if you choose to display it under SQL Server’s grouping rule. Do not group by a measure such as Amount just because it appears in output; that would split customers into separate groups whenever their orders have different amounts.
DISTINCT and GROUP BY can both produce a list of unique status values in simple queries, but consider the intended next step. If you need only the unique labels, DISTINCT communicates that request directly. If you need counts or totals per label, GROUP BY expresses the groups on which those aggregates are calculated. Neither construct fixes an incorrect join key or an unclear grain. Start from the row meaning, then select the SQL operation that states it.
Common mistakes and practice
Common mistakes include selecting a non-aggregated column absent from GROUP BY, grouping by too many or too few columns, and misreading the grain. Do not add GROUP BY just to hide repeated values from a join: first decide whether you want detail or a summary. With a LEFT JOIN, count a nullable right-side key such as COUNT(o.OrderId), not COUNT(*), because the preserved customer row exists even when it has no order. Remember that SUM for that unmatched customer is NULL unless you deliberately use COALESCE. See LEFT JOIN behavior when deciding which side to preserve.
Practice with the demo:
- Count orders by status.
- Calculate total sales by status.
- Find the order count and total by customer.
- Show every customer, including those without orders, and their order count.
- Describe the grain of a query grouped by Status and CustomerId.
Answers
-- 1: Complete 4, Pending 2, Shipped 1, Processing 1
SELECT Status, COUNT(*) AS OrderCount FROM dbo.Orders GROUP BY Status;
-- 2: Complete 449.49, Pending 335.00, Shipped 82.75, Processing NULL
SELECT Status, SUM(Amount) AS Sales FROM dbo.Orders GROUP BY Status;
-- 3: one row per customer with orders
SELECT CustomerId, COUNT(*) AS OrderCount, SUM(Amount) AS Amount
FROM dbo.Orders GROUP BY CustomerId;
-- 4: ten rows, including six customers with zero orders
SELECT c.CustomerId, COUNT(o.OrderId) AS OrderCount
FROM dbo.Customers AS c LEFT JOIN dbo.Orders AS o ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerId;Once groups exist, the next question is how to filter them. Continue to WHERE vs HAVING, which distinguishes filtering input rows from filtering completed groups.
The Foundations roadmap shows the full beginner path and which lessons are available.
Check what you learned
2 quick questions
SQL Server Foundations
Lesson 14 of 25
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetCheck SQL Server Table SizesMeasure SQL Server table row counts and storage usage, separate data from index space, and identify which tables are driving database growth.Open cheat sheet
- RoadmapSQL Server FoundationsLearn how GROUP BY turns SQL Server rows into one summary row per status, customer, or combination of values.View roadmap
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.