Aggregate Functions

Learn how to summarize data in SQL Server using aggregate functions: COUNT, SUM, AVG, MIN, and MAX.

Introduction

Beyond retrieving individual rows, SQL Server can summarize entire columns of data using aggregate functions. These functions take a set of values and reduce them to a single summary value — a count, a total, an average, or an extreme value. Aggregate functions are the foundation of nearly every report and dashboard query.

What You Will Learn

How COUNT counts rows or non-NULL values.
How SUM totals numeric columns.
How AVG computes an average.
How MIN and MAX find extreme values.

The examples below assume a fresh Orders table with sample data.

Sample Data

CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName VARCHAR(100),
    OrderTotal DECIMAL(10,2),
    OrderDate DATE
);

INSERT INTO Orders (CustomerName, OrderTotal, OrderDate)
VALUES
    ('Ravi Traders', 4599.00, '2024-01-05'),
    ('Meera Retail', 1250.00, '2024-01-10'),
    ('Ravi Traders', 3200.00, '2024-02-02'),
    ('Anil Stores', 890.00, '2024-02-15'),
    ('Meera Retail', 6100.00, '2024-03-01');

COUNT

COUNT(*) returns the total number of rows in a result set. COUNT(ColumnName) returns the number of non-NULL values in that specific column, which can be lower than COUNT(*) if the column contains NULLs.

COUNT

SELECT COUNT(*) AS TotalOrders
FROM Orders;

Result

Click Run to see what this code prints.

SUM

SUM adds up all the values in a numeric column, returning a single total. NULL values are ignored automatically.

SUM

SELECT SUM(OrderTotal) AS TotalRevenue
FROM Orders;

Result

Click Run to see what this code prints.

AVG

AVG calculates the arithmetic mean of a numeric column. Like SUM, it ignores NULL values when computing both the total and the count used in the average.

AVG

SELECT AVG(OrderTotal) AS AverageOrderValue
FROM Orders;

Result

Click Run to see what this code prints.

MIN and MAX

MIN returns the smallest value in a column, and MAX returns the largest. They work on numeric, date, and even string columns (using alphabetical comparison for strings).

MIN and MAX

SELECT
    MIN(OrderTotal) AS SmallestOrder,
    MAX(OrderTotal) AS LargestOrder,
    MIN(OrderDate) AS EarliestOrder,
    MAX(OrderDate) AS LatestOrder
FROM Orders;

Result

Click Run to see what this code prints.

Combining Aggregate Functions

Multiple aggregate functions can be combined in a single SELECT to produce a compact summary in one query, which is far more efficient than running separate queries for each statistic.

Combined Summary

SELECT
    COUNT(*) AS TotalOrders,
    SUM(OrderTotal) AS TotalRevenue,
    AVG(OrderTotal) AS AvgOrderValue,
    MIN(OrderTotal) AS SmallestOrder,
    MAX(OrderTotal) AS LargestOrder
FROM Orders;

Result

Click Run to see what this code prints.

Aggregates and Non-Aggregated Columns

When a SELECT includes both an aggregate function and a plain column (like CustomerName), SQL Server requires a GROUP BY clause to specify how the plain column relates to the aggregation — otherwise it raises an error. You will learn GROUP BY in the next lesson.

Common Mistakes

Avoid These Mistakes

Mixing aggregate functions with non-aggregated columns without a GROUP BY, which causes an error.
Assuming COUNT(ColumnName) behaves the same as COUNT(*) — it excludes NULL values while COUNT(*) does not.
Forgetting that SUM and AVG silently ignore NULL values, which can skew results if NULLs represent missing rather than zero values.
Using MIN/MAX on a mixed-type or improperly typed column and getting unexpected alphabetical rather than numeric ordering.

Best Practices

Use COUNT(*) for row counts and COUNT(ColumnName) when you specifically need non-NULL counts.
Alias aggregate results with meaningful names for clarity in reports.
Be explicit about how NULLs should be handled — use ISNULL or COALESCE if zero values are intended instead of NULL.
Combine related aggregates into a single query rather than issuing several separate queries.

Frequently Asked Questions

Do aggregate functions ignore NULL values?

Yes, all of COUNT(column), SUM, AVG, MIN, and MAX ignore NULL values in their calculations. COUNT(*) is the exception — it counts all rows regardless of NULLs.

Can I use aggregate functions in a WHERE clause?

No. WHERE filters individual rows before aggregation happens. To filter based on an aggregated value, you need the HAVING clause, covered in the next lesson.

What does AVG return if the column has zero non-NULL values?

It returns NULL, since there is nothing to average. Dividing by zero rows is avoided by SQL Server internally.

Key Takeaways

COUNT, SUM, AVG, MIN, and MAX summarize sets of values into a single result.
COUNT(*) counts all rows; COUNT(column) counts only non-NULL values.
SUM and AVG ignore NULL values automatically.
MIN and MAX work on numeric, date, and string columns.
Mixing aggregates with plain columns requires GROUP BY.

Summary

Aggregate functions let you summarize data at the table level. Next, you will learn how to apply those same functions per group of rows using GROUP BY, and how to filter those groups with HAVING.

Next Lesson →

GROUP BY & HAVING