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.
Topics on this page
Section titled “Topics on this page”| 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. |
Sample data
Section titled “Sample data”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 statement
Section titled “SELECT statement”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 VatAmountFROM dbo.Orders AS oWHERE o.Status = 'Shipped'ORDER BY VatAmount DESC; -- the alias is visible here, not in WHEREExpected result:
Id OrderDate Amount VatAmount1001 2026-10-02 120.00 27.60001002 2026-10-15 80.50 18.51501005 2026-11-03 15.25 3.5075Pitfalls:
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.DISTINCTas a bug fix. If a join produces duplicates, find the join that multiplies rows instead of hiding it withDISTINCT.- Schema-qualify objects. Write
dbo.Orders, notOrders; it avoids name-resolution surprises when users have different default schemas.
Filtering with WHERE
Section titled “Filtering with WHERE”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 customerSELECT Id, Name, CountryFROM dbo.CustomersWHERE (Country = 'US' AND IsActive = 1) OR Country = 'CA'ORDER BY Name;Expected result:
Id Name Country2 Ben Ortiz US4 Dana Fox CAPitfalls:
- Missing parentheses.
Country = 'US' AND IsActive = 1 OR Country = 'CA'happens to mean the same thing here, butCountry = 'US' OR Country = 'CA' AND IsActive = 1does not: it returns inactive US customers. BETWEENwith 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.
Sorting and paging
Section titled “Sorting and paging”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, AmountFROM dbo.OrdersWHERE Status <> 'Cancelled'ORDER BY Amount DESC, Id -- Id breaks tiesOFFSET (@PageNumber - 1) * @PageSize ROWSFETCH NEXT @PageSize ROWS ONLY;Expected result (page 2 of the non-cancelled orders):
Id OrderDate Amount1002 2026-10-15 80.501003 2026-10-20 45.00Pitfalls:
- Relying on insertion or key order. A query without
ORDER BYmay look sorted in testing and come back in a different order in production. - Deep
OFFSETpages are slow.OFFSET 100000 ROWSstill reads and discards 100,000 rows. For “load more” feeds use keyset paging:WHERE (Amount < @LastAmount) OR (Amount = @LastAmount AND Id > @LastId). TOPwithoutORDER BYreturns an arbitrary N rows, not the “first” ones.
NULL handling
Section titled “NULL handling”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 ContactEmailFROM dbo.CustomersORDER BY Id;
SELECT COUNT(*) AS AllCustomers, COUNT(Email) AS WithEmailFROM dbo.Customers;Expected result:
Name ContactEmailAda Lopez ada@example.testBen Ortiz ben@example.testChen Wei (no email)Dana Fox dana@example.test
AllCustomers WithEmail4 3Pitfalls:
= NULLnever matches.WHERE Email = NULLreturns no rows even though Chen Wei has no email. UseIS NULL.ISNULLcan truncate.ISNULL(ShortCode, 'UNKNOWN')whereShortCodeisCHAR(3)returns'UNK', because the result takes the first argument’s type.COALESCEdoes not have this problem.NOT INwith aNULLin the list returns no rows at all; see Subqueries.
Aggregate functions
Section titled “Aggregate functions”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 LastOrderFROM dbo.OrdersWHERE Status <> 'Cancelled';Expected result:
OrderCount Revenue AverageOrder FirstOrder LastOrder4 260.75 65.187500 2026-10-02 2026-11-03Pitfalls:
- Integer averages.
AVG(Quantity)over1, 2returns1. UseAVG(CAST(Quantity AS decimal(10,2))). SUMover no rows isNULL, 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.
Grouping with GROUP BY and HAVING
Section titled “Grouping with GROUP BY and HAVING”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 50SELECT CustomerId, COUNT(*) AS OrderCount, SUM(Amount) AS TotalFROM dbo.OrdersWHERE Status <> 'Cancelled' -- row filter, before groupingGROUP BY CustomerIdHAVING SUM(Amount) >= 50 -- group filter, after groupingORDER BY Total DESC;Expected result:
CustomerId OrderCount Total1 2 200.50Customer 2 is filtered out because, once the cancelled order is excluded in WHERE, only 45.00 remains.
Pitfalls:
- Aggregates in
WHERE.WHERE SUM(Amount) > 1000raises error 147; move the condition toHAVING. - Filtering in the wrong place changes the answer. Moving
Status <> 'Cancelled'fromWHEREintoHAVINGis not even valid here, and moving it into aCASEinsideSUMgives different counts. Decide whether a row should exist in the group at all. - Grouping by a non-unique label. Group customers by
Id(andNameif you display it), never byNamealone: two customers can share a name.
Choosing between them
Section titled “Choosing between them”| 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 |
Interview questions
Section titled “Interview questions”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.
Related pages
Section titled “Related pages”- SQL Server introduction: editions, tools, key terms and the reading path for this section.
- Joins, subqueries and CTEs: combining
CustomersandOrders. - Functions and stored procedures: string, date and conversion functions used inside queries.
- Tables, constraints and indexes: how the sample tables are designed and indexed.
- SQL Server interview questions
Official references
Section titled “Official references”- SELECT (Transact-SQL), including the logical processing order
- WHERE and Operator precedence
- ORDER BY clause, including OFFSET and FETCH
- NULL and UNKNOWN, COALESCE, ISNULL, IS DISTINCT FROM
- Aggregate functions and GROUP BY