SQL Server Aggregate Functions - SUM, AVG, COUNT, and More


Aggregate functions in SQL Server are used to perform calculations on a set of values and return a single result. In this guide, we'll explore common aggregate functions such as SUM, AVG, COUNT, and more, and how to use them in SQL queries with code examples.


Understanding Aggregate Functions

Aggregate functions operate on a set of values and return a single value that summarizes the data. They are commonly used in tasks like calculating totals, averages, and counting records.


Basic Syntax

The basic syntax of an aggregate function in a SQL query looks like this:


-- Aggregate function usage
SELECT AGGREGATE_FUNCTION(column_name)
FROM table_name
WHERE condition;

Common Aggregate Functions

Here are some common aggregate functions in SQL Server:


  • SUM: Calculates the sum of numeric values.
  • AVG: Calculates the average of numeric values.
  • COUNT: Counts the number of rows.
  • MAX: Retrieves the maximum value from a set.
  • MIN: Retrieves the minimum value from a set.

Using Aggregate Functions

Here's an example of using the SUM and AVG functions to calculate the total and average salary of employees:


-- Calculate total and average salary
SELECT SUM(Salary) AS TotalSalary, AVG(Salary) AS AverageSalary
FROM Employees;

Grouping Data

Aggregate functions are often used with the GROUP BY clause to group data and perform calculations within each group. For example, to find the total sales per product category:


-- Calculate total sales per product category
SELECT Category, SUM(Sales) AS TotalSales
FROM SalesData
GROUP BY Category;

Filtering Data

You can also use aggregate functions in combination with the HAVING clause to filter groups based on specific criteria. For instance, to find product categories with total sales exceeding a certain threshold:


-- Find categories with total sales exceeding a threshold
SELECT Category, SUM(Sales) AS TotalSales
FROM SalesData
GROUP BY Category
HAVING SUM(Sales) > 10000;

What's Next?

You've learned the basics of SQL Server aggregate functions, including SUM, AVG, COUNT, and more. To become proficient, you can explore advanced topics such as nested aggregates, using CASE within aggregates, and handling NULL values in calculations.


Aggregate functions are essential for summarizing and analyzing data in SQL Server databases.