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.