In this guide, I'll walk through the most commonly used aggregate functions in SQL Server, such as SUM(), COUNT(), AVG(), MIN(), and MAX(). Each function is explained with clear, easy to understand examples so you can see exactly how they work in real world scenarios. Whether you're writing reports, building dashboards, or optimizing queries, mastering these functions will help you write cleaner, faster, and more efficient SQL code.
👉 Throughout this tutorial, I'll use the Books table as a sample dataset to test and illustrate how SQL Server aggregate functions work.
1) COUNT() Function
The COUNT() function is one of the most commonly used aggregate functions in SQL Server. It retuns the number of rows that match a particular (or specified) condition or expression.
Syntax
COUNT(expression)
• expression: This can be a column name, *, or any valid SQL expression.
Example 1.
SELECT COUNT(*) AS TotalBooks FROM dbo.Books -- Output: 9
It returns the total number of rows in the table.
Example 2.
In the second example, the COUNT(Price) returns the count of rows where "price" is not null.
SELECT COUNT(Price) AS PricedBooks FROM dbo.Books -- Possible Output: 9
Example 3. This query will return the count of Distinct categories.
SELECT COUNT(DISTINCT Category) AS UniqueCategories FROM dbo.Books -- Output: 4
Example 4. Count with GROUP BY, will count how many books exist in each category.
SELECT Category, COUNT(*) AS BooksPerCategory FROM dbo.Books GROUP BY Category
2) SUM() Function
The SUM() function in SQL Server is an aggregate function used to calculate the total (sum) of numeric values in a column. It's commonly used in reporting, analytics, and data summaries.
Syntax
SUM(expression)
• expression: A numeric column or calculation you want to total.
• It ignores NULL values automatically.
• The SUM() function must be used with GROUP BY clause if you are aggregating by categories or groups.
Example 1. Get the total of all prices in the Books table.
SELECT SUM(Price) AS TotalPrice FROM dbo.Books -- Output: 1153.55
Example 2. Using GROUP BY clause: This calculates the total price of books per category.
SELECT Category, SUM(Price) AS TotalPrice FROM dbo.Books GROUP BY Category
Example 3. With WHERE Clause: This totals only the prices of books in specified category.
SELECT SUM(Price) AS BooksTotal FROM dbo.Books WHERE Category IN ('Programming', 'Computers')
Example 4. With HAVING Clause: This returns only those categories where the total price exceeds 500.
SELECT Category, SUM(Price) AS TotalPrice FROM dbo.Books GROUP BY Category HAVING SUM(Price) > 500
3) AVG() Function
The AVG() function is an aggregate function in SQL Server that calculates the average (mean) of a set of numeric values. It’s commonly used to analyze trends, performance, and pricing across datasets.
Syntax
AGV(expression)
• expression: A numeric column or calculation whose average you want to compute.
• It ignores NULL values automatically.
• Must be used with GROUP BY if you're calculating averages per group.
Example 1. Get the average price of all Books.
SELECT AVG(Price) AS AveragePrice FROM dbo.Books
Example 2. Calculate the average price of books per Category.
SELECT Category, AVG(Price) AS AveragePrice FROM dbo.Books GROUP BY Category
Example 3. With WHERE Clause: Calculate the average price of books that cost more than 200.
SELECT AVG(Price) AS HighAverage FROM dbo.Books WHERE Price > 200
4) MIN() Function
The MIN() function is an aggregate function in SQL Server that returns the smallest (minimum) value in a set of numeric, date, or string values. It’s useful for identifying the lowest price, earliest date, or alphabetically first item in a dataset.
Syntax
MIN(expression)
• expression: A column or calculation whose minimum value you want to find.
• It ignores NULL values automatically.
• Must be used with GROUP BY if you're calculating averages per group.
Example 1. Get the "minimum" price among all books in the Books table.
SELECT MIN(Price) AS LowestPrice FROM dbo.Books
Example 2. With WHERE Clause: Get the lowest price among books that cost more than 100.
SELECT MIN(Price) AS CheapestPremiumBook FROM dbo.Books WHERE Price > 100
Example 3. Using MIN() Function with Variable: You can use the MIN() function with a variable in SQL Server like this:
-- 1. DECLARE @LowestPrice DECIMAL(10,2); SELECT @LowestPrice = MIN(Price) FROM dbo.Books; SELECT @LowestPrice AS LowestBookPrice -- 2. DECLARE @LowestPrice DECIMAL(10,2); SELECT @LowestPrice = MIN(Price) FROM dbo.Books; SELECT @LowestPrice -- 3. Using a table variable to store multiple values and get the minimum value. DECLARE @LowestPrice TABLE (PRICE DECIMAL(10,2)); INSERT INTO @LowestPrice VALUES (10.5), (120.75), (30); SELECT MIN(Price) FROM @LowestPrice
5) MAX() Function
The MAX() function is an aggregate function in SQL Server that returns the largest (maximum) value from a set of values. It works with numeric, date/time, and string data types, making it versatile for many real-world scenarios.
Syntax
MAX(expression)
• expression: A column or calculation whose maximum value you want to find.
• It ignores NULL values automatically.
• Can be used with GROUP BY to find maximums per group.
Example 1. Find the most expensive book in the dbo.Books table.
SELECT MAX(Price) AS HighestPrice FROM dbo.Books
Example 2. Find the Longest Book Title (Alphabetically Last)
SELECT MAX(BookName) AS LastAlphabeticalTitle FROM dbo.Books -- Teaching Science
6) COUNT(DISTINCT) Function
The COUNT(DISTINCT) function is an aggregate function that returns the number of unique non-NULL values in a column. It’s especially useful when you want to eliminate duplicates from your count.
Syntax
COUNT(DISTINCT expression)
• expression: A column or calculation whose distinct values you want to count.
• It ignores NULL values automatically.
• Cannot be used with multiple columns directly (only one expression allowed).
Example 1. Count the number of distinct categories in the Books table.
SELECT COUNT(DISTINCT Category) AS UniqueCategories FROM dbo.Books
Example 2. Counts how many different categories in the price range 100 and 150.
SELECT COUNT(DISTINCT Category) AS UniqueCategory FROM dbo.Books WHERE Price >= 100 and Price <= 150
👉 Remember, there's a difference between COUNT() and COUNT(DISTINC) function
• COUNT(Column): Counts all non-NULL values, including duplicates.
• COUNT(DISTINCT Column): Counts only unique non-NULL values, ignoring duplicates.
For example,
SELECT COUNT(Category) AS TotalCategories, COUNT(DISTINCT Category) AS UniqueCategories FROM dbo.Books -- Output: TotalCategories - 9, UniqueCategories - 4
7) CHECKSUM_AGG() Function
CHECKSUM_AGG() is an aggregate function that returns a checksum value for a group of rows. It computes a single integer that represents all the values in a column (or expression).
It is often used to "detect" changes in a table or dataset. It can also be used to quickly compare whether two rows are identical.
Syntax
CHECKSUM_AGG(DISTINCT expression)
• expression: Colomn name or expression to aggregate.
• DISTINCT: Removes duplicates before computing checksum. This is optional.
Example 1. Basic checksum of a column.
Note: The column used with the CHECKSUM_AGG() function must be of an integer data type. Since the books table from the previous examples doesn't contain any integer columns, let's create a new table named sales for demonstration purposes.
CREATE TABLE Sales ( SaleID INT, Amount INT );
Now, insert few rows in the table.
INSERT INTO dbo.Sales (SaleID, Amount) values (1, 200) INSERT INTO dbo.Sales (SaleID, Amount) values (2, 600) INSERT INTO dbo.Sales (SaleID, Amount) values (3, 180) INSERT INTO dbo.Sales (SaleID, Amount) values (4, 200)
Using CHECKSUM_AGG() on the "amount" column.
SELECT CHECKSUM_AGG(Amount) AS AmountChecksum FROM Sales -- Output: AmountChecksum - 748
Example 2. Using DISTINCT
SELECT CHECKSUM_AGG(DISTINCT Amount) AS DistinctAmountChecksum FROM Sales; -- Output: AmountChecksum - 548
What exactly does these queries do? Is it useful in real-world scenarios?
👉 First, see the output of both the queries. Did you find any difference? Let me explain.
See the data that I have inserted in the "Sales" table again. The first and last row amount is same. The sales table amounts are.
200
600
180
200
Therefore, the first query resulted in - total amount: 748.
However, DISTINCT amount becomes,
200
600
180
So, the total amount is: 548
A checksum is an integer that represents a set of values. It does not show the values themselves, but it creates a numeric signature for them.
🤔 Is CHECKSUM_AGG() function useful?
Yes, it is useful for quick change detection. Suppose, you compute a checksum now, store it, and compute it again later.
If someone inserts, deletes, or updates an amount, the "checksum" changes.
This is faster than comparing row by row.
8) GROUPING() Function
The GROUPING() function in SQL Server is a bitwise function used with GROUP BY and GROUPING SETS, ROLLUP and CUBE to indicate whether a column in the resultset is aggregated (or rolled up) or represents an actual group value.
It returns 0 or 1.
0: The column is part of the grouped result or NOT aggregated.
1: The column is aggregated.
Syntax
GROUPING ( column_name )
GROUPING() can only be used inside SELECT when using GROUP BY ROLLUP, GROUP BY CUBE and GROUPING SETS.
Example 1: Using GROUPING() with ROLLUP
For this example, I am using the db.books table.
SELECT Category, COUNT(*) AS TotalBooks, GROUPING(Category) AS CategoryTotal FROM dbo.Books GROUP BY ROLLUP(Category)
Result:
Note: The NULL values appear in the last row because of the ROLLUP operation. These NULLs do not mean missing data, they mean, "This row is a rolled-up total, so this column has no specific value."
Example 2: Replace ROLLUP NULLs with Labels
In the example, I am using the db.Employee table.
SELECT CASE WHEN GROUPING(Department) = 1 THEN 'All Departments' ELSE Department END AS Department, CASE WHEN GROUPING(YEAR(JoiningDate)) = 1 THEN 'All Years' ELSE CAST(YEAR(JoiningDate) AS VARCHAR(4)) END AS HireYear, COUNT(*) AS TotalEmployees FROM Employee GROUP BY ROLLUP (Department, YEAR(JoiningDate));
