T-SQL Tutorial

T-SQL Aggregate functions


SQL Server AGGREGATE functions are special functions in SQL Server that operate on a set of values and return a single value as a result. These functions are commonly used to perform calculations on data in a database and are essential for summarizing large amounts of data.

This article describes how to use the T-SQL Aggregate functions in SQL Server database.
Aggregate functions perform a calculation on a set of values and return a single value.
The SQL Server aggregate functions are: AVG, SUM, MAX, MIN, COUNT.

SQL Server Aggregate functions are used to calculate and return the sum, average, minimum, or maximum value of a column in a table. You can also use it to calculate the number of rows in a table or the distinct values in a column.

The most commonly used SQL Server Aggregate functions include:


AVG

The T-SQL AVG function is used to calculate the average of all values in a specified column.

select avg(price) from courses;
select name, price
from courses
group by name, price
having avg(price) > 70;


SUM

The T-SQL SUM function is used to calculate the sum of all values in a specified column.

select sum(price) from courses;
select sum(price)
from courses
where price > 50;


MAX

The T-SQL MAX function returns the max value.

select max(price) from courses;
select max(price)
from courses
where price < 70;


MIN

The T-SQL MIN function returns the min value.

select min(price) from courses;
select min(price)
from courses
where price > 70;


COUNT

The T-SQL COUNT function returns the number of rows returned by a query.

select count(*) from courses;
select count(*), price
from courses
group by price
having count(*) = 2;


Some other Aggregate functions that are less commonly used but still useful include:

STDEV This function is used to calculate the standard deviation of all values in a specified column.
VAR This function is used to calculate the variance of all values in a specified column.

Aggregate functions can be used with the GROUP BY clause to group the result set by one or more columns. This allows for more complex calculations and summaries of data.

In summary, SQL Server Aggregate functions are powerful tools that can be used to perform a variety of calculations and analyses on large datasets. Understanding these functions is essential for working with SQL Server and can greatly simplify and enhance your data analysis efforts.