SQL Server
Aggregate Functions in SQL Server: COUNT, SUM, AVG, MIN, and MAX
Learn how SQL Server aggregate functions summarize customer and order rows with COUNT, SUM, AVG, MIN, and MAX.
On this page
So far, the queries in SQL Server Foundations have returned customer or order rows. Sometimes the question is broader: how many orders exist, what is their total value, or which order is largest? An aggregate function reads values from multiple rows and summarizes them into a result value. This lesson uses the ten customers and eight orders in JaviBuildsLab and prepares you to summarize each group. Review NULL values before counting optional fields, and use WHERE when you need to choose which rows enter the summary.
Count rows and known values
COUNT(*) counts rows in the input:
SELECT COUNT(*) AS CustomerCount
FROM dbo.Customers;The demo contains ten customer rows, so CustomerCount is 10. The asterisk means count the rows, regardless of whether an individual column in a row contains NULL. It does not mean count every cell.
COUNT(column) instead counts only rows where that column has a non-NULL value:
SELECT COUNT(Email) AS CustomersWithEmail
FROM dbo.Customers;The result is 9 because Isla’s email is NULL. As the NULL lesson explains, missing and empty values differ. This is why COUNT(*) and COUNT(Email) answer different questions: how many customer rows are present, and how many contain a known email. COUNT returns an integer count.
- dbo.Orders: 8 rows
- COUNT(*) / SUM / AVG / MIN / MAX
- One summary row
Sum and average values
SUM adds non-NULL numeric values. Amount is a decimal column, so the sum is a decimal amount:
SELECT SUM(Amount) AS TotalSales
FROM dbo.Orders;The seven known order amounts are 125.50, 49.99, 310.00, 82.75, 199.00, 25.00, and 75.00. Their sum is 867.24. This is the total of the values in the selected rows; it does not mean that every order has the same amount.
AVG calculates the arithmetic mean: it adds the non-NULL values and divides by the number of non-NULL values. One of the eight Amount values is NULL, so the mean uses seven known values: 867.24 divided by 7, approximately 123.8914. SQL Server returns a numeric decimal result for this decimal input. A results grid may show several decimal places; do not assume the average is a currency-rounded value. If a report needs a particular display scale, choose an explicit conversion or rounding rule for that reporting requirement. The stored order values remain unchanged.
SELECT AVG(Amount) AS AverageOrderValue
FROM dbo.Orders;An average describes the selected set, not a typical guarantee for every row. A few large values can pull it upward. Always check which rows enter the calculation, and do not interpret the average as a median or as a promise that an order has that exact amount.
Find minimum and maximum values
MIN returns the smallest non-NULL value and MAX returns the largest. They work with dates as well as numbers:
SELECT
MIN(OrderDate) AS FirstOrderDate,
MAX(OrderDate) AS LatestOrderDate
FROM dbo.Orders;For this dataset the first order date is 2025-02-01 and the latest is 2025-07-04. With numeric data, the same functions identify the smallest and largest amount. They return the value itself, not the row that contains it; select and relate a key separately if you need the full order record.
Several aggregate expressions can share one query:
SELECT
COUNT(*) AS OrderCount,
SUM(Amount) AS TotalSales,
AVG(Amount) AS AverageOrderValue,
MIN(Amount) AS SmallestOrder,
MAX(Amount) AS LargestOrder
FROM dbo.Orders;The output is one row: 8 orders, 867.24 total, an average of approximately 123.8914, a smallest amount of 25.00, and a largest of 310.00. Since there is no GROUP BY, SQL Server summarizes the complete input as one set. The single row is not an order-level listing.
Filter rows before summarizing
Aggregate functions summarize the rows left by FROM and WHERE. For example, this counts and totals completed orders:
SELECT
COUNT(*) AS CompletedOrders,
SUM(Amount) AS CompletedSales
FROM dbo.Orders
WHERE Status = N'Complete';The actual status in the demo is Complete (singular). Four rows match, totaling 449.49. WHERE removes other rows first; only the matching order amounts reach COUNT and SUM. This is the same row filtering you learned in the WHERE lesson. Next, GROUP BY will calculate separate summaries for each status or customer.
NULL and DISTINCT behavior
COUNT(*) counts input rows even when columns on those rows are NULL. COUNT(column) ignores NULL values in that column. SUM, AVG, MIN, and MAX also ignore NULL inputs; they operate on the non-NULL values they receive. If a group has no non-NULL values, these value aggregates return NULL. When a query has no matching rows at all, COUNT(*) returns 0, while SUM, AVG, MIN, and MAX return NULL. In the current demo, Amount is nullable and one order has an unknown amount; OrderDate is NOT NULL. The Amount examples below show how aggregates treat that row.
You can ask COUNT to count unique non-NULL values with DISTINCT:
SELECT COUNT(DISTINCT CustomerId) AS CustomersWithOrders
FROM dbo.Orders;The answer is 4: customers 1, 2, 4, and 7 have orders. DISTINCT is applied to the values being counted; it does not change the underlying table. Use it when the question is about unique customers, not as a generic way to conceal unexpected rows.
Choose the aggregate that matches the question
Start by naming the thing you want to know. “How many records?” usually calls for COUNT(*). “How many records have a known value in this optional field?” calls for COUNT(column). “What do the numeric values add up to?” suggests SUM. “What is their arithmetic mean?” suggests AVG. “What is the earliest or latest date?” suggests MIN and MAX. These functions return different kinds of answers, so a function that happens to produce a plausible number may still answer the wrong question.
For example, counting Email does not measure the number of customers: a customer can exist without a known email. Counting OrderId counts orders because each order has a non-NULL identifier. Counting DISTINCT CustomerId answers how many different customers appear among the input orders. In a future grouped query, those choices can be made per group, but here each expression summarizes the full input set.
The sum and average are also tied to the selected rows. If you add a WHERE condition, they no longer describe all orders. If you join Orders to a one-to-many detail table, the same order amount might appear once per detail row; summing it after that join could count it repeatedly. Keep the row grain in view, as discussed in the join result-grain lesson. Aggregate functions do not know whether a repeated amount is an accidental multiplication or a value intended to appear more than once; they calculate over the rows provided.
For numeric amounts, remember what AVG’s denominator represents. SQL Server averages the non-NULL values, not the count of all rows when some amounts are NULL. If the selected rows include eight orders and one amount is unknown, the average uses the seven known amounts. That may be correct, but the report should make clear that it is an average of recorded values. A separate COUNT(Amount) can help show how many values contributed. Do not replace a missing amount with zero unless zero is a valid business meaning: zero is a known amount, whereas NULL means no known amount.
MIN and MAX can be useful for boundary checks: an earliest date may reveal the first recorded activity, while a largest amount may identify an unusually large transaction for follow-up. They do not identify a row by themselves. Two orders can have the same maximum amount, so selecting a customer name beside MAX(Amount) without grouping or a separate query does not tell SQL Server which name to return. The next lesson explains how to summarize by a key and obtain one aggregate value for each group.
Common mistakes
Do not substitute COUNT(column) for COUNT(*) without deciding how NULL should be treated. Do not expect an aggregate query without grouping to return order detail: its output summarizes the whole input. Check whether WHERE excludes rows before interpreting a result. Read AVG as a mean, and pay attention to its numeric result rather than assuming a two-decimal currency display. Finally, when a query includes both a normal column and aggregates, define what one result row should represent; lesson 14 explains why the non-aggregated value must be grouped.
Practice
Try these questions against JaviBuildsLab before reading the answers:
- How many customer rows are there, and how many have an email?
- What is the total and average order value?
- What are the earliest and latest order dates?
- How many distinct customers have placed an order?
- How many
Pendingorders are present?
Answers
-- 1: 10 rows; 9 non-NULL email values
SELECT COUNT(*) AS CustomerRows, COUNT(Email) AS KnownEmails FROM dbo.Customers;
-- 2: 867.24 total; average approximately 123.8914
SELECT SUM(Amount) AS Total, AVG(Amount) AS Average FROM dbo.Orders;
-- 3: 2025-02-01 and 2025-07-04
SELECT MIN(OrderDate) AS FirstDate, MAX(OrderDate) AS LastDate FROM dbo.Orders;
-- 4: 4
SELECT COUNT(DISTINCT CustomerId) AS CustomersWithOrders FROM dbo.Orders;
-- 5: 2
SELECT COUNT(*) AS PendingOrders FROM dbo.Orders WHERE Status = N'Pending';Keep the input set in mind whenever you read an aggregate. The next question is often not just “what is the total?” but “what is the total for each status or customer?”
Check what you learned
2 quick questions
SQL Server Foundations
Lesson 13 of 25
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetFind Stored Procedure UsageFind recently executed stored procedures, compare execution counts and resource usage, and understand the limits of SQL Server procedure-cache statistics.Open cheat sheet
- RoadmapSQL Server FoundationsLearn how SQL Server aggregate functions summarize customer and order rows with COUNT, SUM, AVG, MIN, and MAX.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.