Skip to content

Querying data in SQL Server: SELECT to GROUP BY

Almost every conversation with a database starts with a SELECT. This page walks through the parts of a single-table query in the order you usually learn them: choose columns, filter rows, sort and page, deal with missing values, then summarize rows into totals and groups. Joins, subqueries and common table expressions, which combine several tables, have their own page: Joins, subqueries and CTEs.

All samples use the T-SQL dialect of SQL Server and also work in Azure SQL Database and Azure SQL Managed Instance unless a section says otherwise. No database was available while this page was written, so the samples were not run here: the syntax was checked against the Microsoft Learn Transact-SQL reference, and each “Expected result” was worked out from the sample data below (the portable parts were cross-checked with SQLite). Run them yourself in SQL Server Management Studio (SSMS), the MSSQL extension for Visual Studio Code, or sqlcmd.

Topic Use it when
SELECT statement You read columns or computed values from a table.
Filtering with WHERE You only want rows that match a condition.
Sorting and paging You need a guaranteed order, the top N rows, or one page of results.
NULL handling A column can be missing or unknown.
Aggregate functions You need a count, total, average, minimum or maximum.
Grouping with GROUP BY and HAVING You need one summary row per customer, status, month and so on.

Every sample on this page queries two small OrdersPortal tables. Create them in a scratch database (not run here):

CREATE TABLE dbo.Customers (
Id INT NOT NULL PRIMARY KEY,
Name NVARCHAR(100) NOT NULL,
Email NVARCHAR(320) NULL,
Country CHAR(2) NOT NULL,
IsActive BIT NOT NULL
);
CREATE TABLE dbo.Orders (
Id INT NOT NULL PRIMARY KEY,
CustomerId INT NOT NULL REFERENCES dbo.Customers (Id),
OrderDate DATE NOT NULL,
Status VARCHAR(20) NOT NULL,
Amount DECIMAL(10,2) NOT NULL
);
INSERT INTO dbo.Customers (Id, Name, Email, Country, IsActive)
VALUES (1, N'Ada Lopez', N'ada@example.test', 'PT', 1),
(2, N'Ben Ortiz', N'ben@example.test', 'US', 1),
(3, N'Chen Wei', NULL, 'US', 0),
(4, N'Dana Fox', N'dana@example.test', 'CA', 1);
INSERT INTO dbo.Orders (Id, CustomerId, OrderDate, Status, Amount)
VALUES (1001, 1, '2026-10-02', 'Shipped', 120.00),
(1002, 1, '2026-10-15', 'Shipped', 80.50),
(1003, 2, '2026-10-20', 'Pending', 45.00),
(1004, 2, '2026-11-01', 'Cancelled', 300.00),
(1005, 4, '2026-11-03', 'Shipped', 15.25);

Chen Wei has no email and no orders, which is deliberate: missing data is where most query bugs hide.

SELECT has been the core of T-SQL since the first SQL Server release. It returns a result set: the columns you list in the select list, read from the tables in the FROM clause, optionally filtered, grouped and sorted by the clauses that follow. You can return stored columns, expressions such as Amount * 1.23, literals and function calls, and rename any of them with a column alias (AS Alias). DISTINCT removes duplicate rows from the result, and TOP (n) limits how many rows come back.

The clauses are written in the order SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY, but SQL Server evaluates them logically as FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. That is why an alias defined in the select list can be used in ORDER BY but not in WHERE.

SELECT o.Id,
o.OrderDate,
o.Amount,
o.Amount * 0.23 AS VatAmount
FROM dbo.Orders AS o
WHERE o.Status = 'Shipped'
ORDER BY VatAmount DESC; -- the alias is visible here, not in WHERE

Expected result:

Id OrderDate Amount VatAmount
1001 2026-10-02 120.00 27.6000
1002 2026-10-15 80.50 18.5150
1005 2026-11-03 15.25 3.5075

Pitfalls:

  • SELECT * in application code. It sends columns the caller never uses, can stop a narrow index from covering the query, and changes shape when someone adds a column. Name the columns.
  • DISTINCT as a bug fix. If a join produces duplicates, find the join that multiplies rows instead of hiding it with DISTINCT.
  • Schema-qualify objects. Write dbo.Orders, not Orders; it avoids name-resolution surprises when users have different default schemas.

WHERE keeps only the rows for which its condition is true; it also limits which rows an UPDATE or DELETE touches. Conditions combine comparison operators (=, <>, <, >=), AND, OR and NOT, plus BETWEEN (inclusive on both ends), IN (list) and LIKE patterns, where % matches any sequence of characters and _ matches exactly one.

Operator precedence matters: NOT binds before AND, and AND before OR. Use parentheses whenever a condition mixes AND and OR, even when the default happens to be right; the next reader should not have to work it out.

Whether text comparisons are case-sensitive depends on the column’s collation. The default collation for a new installation is usually case-insensitive (…_CI_AS), so 'us' = 'US' is true there.

-- Active US customers, plus every Canadian customer
SELECT Id, Name, Country
FROM dbo.Customers
WHERE (Country = 'US' AND IsActive = 1)
OR Country = 'CA'
ORDER BY Name;

Expected result:

Id Name Country
2 Ben Ortiz US
4 Dana Fox CA

Pitfalls:

  • Missing parentheses. Country = 'US' AND IsActive = 1 OR Country = 'CA' happens to mean the same thing here, but Country = 'US' OR Country = 'CA' AND IsActive = 1 does not: it returns inactive US customers.
  • BETWEEN with date-times. OrderedAt BETWEEN '2026-10-01' AND '2026-10-31' misses everything after midnight on the 31st when the column holds a time. Use a half-open range: >= '2026-10-01' AND < '2026-11-01'.
  • Leading wildcards. LIKE '%@example.test' cannot seek an index; LIKE 'Ada%' can.

ORDER BY is the only thing that guarantees row order. Without it SQL Server returns rows in whatever order the chosen plan produces, which can change after an index is added, statistics are updated or the query goes parallel. Sort by one or more columns or expressions, each ASC (the default) or DESC. NULL values sort first in ascending order.

For “top N” use TOP (n) together with ORDER BY. For pages of results, SQL Server 2012 added OFFSET … ROWS FETCH NEXT … ROWS ONLY, which is part of ORDER BY and therefore requires it. Make the sort key unique (add the primary key as a tie-breaker), or rows with equal values can appear on two pages or on none.

DECLARE @PageNumber int = 2, @PageSize int = 2;
SELECT Id, OrderDate, Amount
FROM dbo.Orders
WHERE Status <> 'Cancelled'
ORDER BY Amount DESC, Id -- Id breaks ties
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

Expected result (page 2 of the non-cancelled orders):

Id OrderDate Amount
1002 2026-10-15 80.50
1003 2026-10-20 45.00

Pitfalls:

  • Relying on insertion or key order. A query without ORDER BY may look sorted in testing and come back in a different order in production.
  • Deep OFFSET pages are slow. OFFSET 100000 ROWS still reads and discards 100,000 rows. For “load more” feeds use keyset paging: WHERE (Amount < @LastAmount) OR (Amount = @LastAmount AND Id > @LastId).
  • TOP without ORDER BY returns an arbitrary N rows, not the “first” ones.

NULL means “unknown or not applicable”; it is not zero and not an empty string. Any comparison with NULL using =, <> or < evaluates to UNKNOWN, which WHERE treats like false. Test for it with IS NULL and IS NOT NULL. Arithmetic and + string concatenation with NULL produce NULL (with the default CONCAT_NULL_YIELDS_NULL ON), while CONCAT() treats NULL as an empty string.

To substitute a value use ISNULL(expr, replacement), which is T-SQL-specific, takes two arguments and returns the data type of the first; or COALESCE(a, b, …), which is ANSI standard, takes any number of arguments and returns the type with the highest precedence. NULLIF(a, b) does the reverse: it returns NULL when the two values are equal, which is handy to avoid division by zero. SQL Server 2022 added IS [NOT] DISTINCT FROM, a comparison that treats two NULLs as equal and always returns true or false.

SELECT Name,
COALESCE(Email, N'(no email)') AS ContactEmail
FROM dbo.Customers
ORDER BY Id;
SELECT COUNT(*) AS AllCustomers, COUNT(Email) AS WithEmail
FROM dbo.Customers;

Expected result:

Name ContactEmail
Ada Lopez ada@example.test
Ben Ortiz ben@example.test
Chen Wei (no email)
Dana Fox dana@example.test
AllCustomers WithEmail
4 3

Pitfalls:

  • = NULL never matches. WHERE Email = NULL returns no rows even though Chen Wei has no email. Use IS NULL.
  • ISNULL can truncate. ISNULL(ShortCode, 'UNKNOWN') where ShortCode is CHAR(3) returns 'UNK', because the result takes the first argument’s type. COALESCE does not have this problem.
  • NOT IN with a NULL in the list returns no rows at all; see Subqueries.

Aggregate functions reduce a set of rows to one value: COUNT, SUM, AVG, MIN and MAX are the everyday ones, with STRING_AGG (SQL Server 2017) for joining text and STDEV/VAR for statistics. Without GROUP BY they collapse the whole filtered result into a single row.

All aggregates except COUNT(*) ignore NULLs. COUNT(*) counts rows, COUNT(column) counts non-null values and COUNT(DISTINCT column) counts distinct non-null values. AVG of an integer column returns an integer (the average is truncated), so cast to decimal first when you need fractions. SUM and AVG of a decimal(10,2) return a decimal(38,…), so the scale of the output can be wider than the column.

SELECT COUNT(*) AS OrderCount,
SUM(Amount) AS Revenue,
AVG(Amount) AS AverageOrder,
MIN(OrderDate) AS FirstOrder,
MAX(OrderDate) AS LastOrder
FROM dbo.Orders
WHERE Status <> 'Cancelled';

Expected result:

OrderCount Revenue AverageOrder FirstOrder LastOrder
4 260.75 65.187500 2026-10-02 2026-11-03

Pitfalls:

  • Integer averages. AVG(Quantity) over 1, 2 returns 1. Use AVG(CAST(Quantity AS decimal(10,2))).
  • SUM over no rows is NULL, not 0. Wrap it: COALESCE(SUM(Amount), 0).
  • Mixing aggregates with plain columns fails with error 8120 (“…is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause”) unless the plain columns are grouped.

GROUP BY produces one output row per distinct combination of the grouping columns, and the aggregates are computed per group. Every column in the select list must be either grouped or inside an aggregate. You can group by expressions, such as YEAR(OrderDate), and SQL Server also supports ROLLUP, CUBE and GROUPING SETS to add subtotal rows in a single query.

WHERE filters rows before grouping, so it cannot reference an aggregate. HAVING filters groups after grouping, so it can. Put row-level conditions in WHERE; it reduces the rows the server has to group.

-- Customers whose non-cancelled orders total at least 50
SELECT CustomerId,
COUNT(*) AS OrderCount,
SUM(Amount) AS Total
FROM dbo.Orders
WHERE Status <> 'Cancelled' -- row filter, before grouping
GROUP BY CustomerId
HAVING SUM(Amount) >= 50 -- group filter, after grouping
ORDER BY Total DESC;

Expected result:

CustomerId OrderCount Total
1 2 200.50

Customer 2 is filtered out because, once the cancelled order is excluded in WHERE, only 45.00 remains.

Pitfalls:

  • Aggregates in WHERE. WHERE SUM(Amount) > 1000 raises error 147; move the condition to HAVING.
  • Filtering in the wrong place changes the answer. Moving Status <> 'Cancelled' from WHERE into HAVING is not even valid here, and moving it into a CASE inside SUM gives different counts. Decide whether a row should exist in the group at all.
  • Grouping by a non-unique label. Group customers by Id (and Name if you display it), never by Name alone: two customers can share a name.
Need Use
The newest 10 orders TOP (10) … ORDER BY OrderDate DESC, Id DESC
Page 3 of a grid ORDER BY … OFFSET … FETCH NEXT with a unique sort key
“Load more” on a large table Keyset paging with WHERE on the last seen key
Fallback for a missing value COALESCE (portable) or ISNULL (two arguments, first argument’s type)
Filter on a total HAVING
Filter on a column value WHERE

What is the logical processing order of a SELECT statement, and why does it matter? FROM (including joins), WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then TOP/OFFSET. It explains why a select-list alias works in ORDER BY but not in WHERE, and why WHERE cannot use aggregates while HAVING can.

What is the difference between COUNT(*), COUNT(Email) and COUNT(DISTINCT Email)? COUNT(*) counts rows, including rows where every column is NULL. COUNT(Email) counts rows where Email is not NULL. COUNT(DISTINCT Email) counts distinct non-null values. In the sample data they return 4, 3 and 3.

When would you choose COALESCE over ISNULL? When you need more than two fallbacks, want ANSI-portable code, or the replacement is longer than the first argument’s type: ISNULL returns the first argument’s type and can truncate. ISNULL is still useful in computed columns because its result is non-nullable when the replacement is not null.

Why can OFFSET/FETCH paging show the same row on two pages? If the ORDER BY key is not unique, rows that tie can be returned in a different order on each execution, so the boundary between pages moves. Add a unique tie-breaker such as the primary key.

Senior scenario: an order-history grid for 40 million orders is fast on page 1 and times out on page 20,000. What do you change? OFFSET must read and discard every earlier row, so cost grows with the page number. Replace it with keyset (seek) paging: the client sends the last (OrderDate, Id) it showed and the query asks for WHERE OrderDate < @LastDate OR (OrderDate = @LastDate AND Id < @LastId) ORDER BY OrderDate DESC, Id DESC, supported by an index on (CustomerId, OrderDate DESC, Id DESC) that includes the displayed columns. Offer “next/previous” instead of “jump to page 20,000”, and return a total count from a cheaper, cached source if the UI needs one.