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.

By JaviPublished 13 min readBeginner

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:

SQL query: One result row per statusT-SQL
SELECT
    Status,
    COUNT(*) AS OrderCount
FROM dbo.Orders
GROUP BY Status;
T-SQL5 linesRead-only query display. It is not connected to a database.

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.

  1. Orders: 8 rows
  2. GROUP BY Status
  3. Complete: 4
  4. Pending: 2
  5. Shipped: 1
  6. Processing: 1
GROUP BY Status creates one row per status value

Calculate an aggregate for each group

GROUP BY is commonly used with aggregate functions. This query calculates total order value for every status:

SQL query: Calculate an aggregate for each groupT-SQL
SELECT
    Status,
    SUM(Amount) AS TotalSales
FROM dbo.Orders
GROUP BY Status;
T-SQL5 linesRead-only query display. It is not connected to a database.

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:

SQL query: Calculate an aggregate for each group (2)T-SQL
SELECT
    CustomerId,
    COUNT(*) AS OrderCount,
    SUM(Amount) AS Amount
FROM dbo.Orders
GROUP BY CustomerId;
T-SQL6 linesRead-only query display. It is not connected to a database.

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:

SQL query: Include customers without ordersT-SQL
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;
T-SQL13 linesRead-only query display. It is not connected to a database.

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:

SQL query: Choose the grain before the columnsT-SQL
SELECT
    Status,
    CustomerId,
    COUNT(*) AS OrderCount
FROM dbo.Orders
GROUP BY
    Status,
    CustomerId;
T-SQL8 linesRead-only query display. It is not connected to a database.

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:

SQL query: GROUP BY, DISTINCT, and orderingT-SQL
SELECT
    Status,
    SUM(Amount) AS Amount
FROM dbo.Orders
GROUP BY Status
ORDER BY Amount DESC;
T-SQL6 linesRead-only query display. It is not connected to a database.

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:

  1. Count orders by status.
  2. Calculate total sales by status.
  3. Find the order count and total by customer.
  4. Show every customer, including those without orders, and their order count.
  5. Describe the grain of a query grouped by Status and CustomerId.

Answers

SQL query: AnswersT-SQL
-- 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;
T-SQL11 linesRead-only query display. It is not connected to a database.

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

Question 1 of 2What does GROUP BY do with rows?
Question 2 of 2What normally belongs in SELECT when you group by a column?

SQL Server Foundations

Lesson 14 of 25

Continue to next lesson

WHERE vs HAVING in SQL Server

Continue to Lesson 15

Tags

  • SQL Server
  • SQL Fundamentals
  • SSMS
  • T-SQL