How to Use Aggregate Functions in SQL Server?

Introduction

Except for COUNT(*), all aggregation functions in SQL Server are built-in functions that do not accept NULL data. An aggregate function returns a single value after calculating a set of values. These functions are mostly used in database query languages' SELECT statements' GROUP BY, and HAVING clauses.

The same value is always returned by aggregate functions when repeatedly called with the same input variables. Deterministic functions are another name for them as a result. It must be noted that the aggregate functions cannot be nested and that the expression cannot be a subquery.

When we use Aggregate functions?

1. The SELECT statement's select list (either a subquery or an outer query).
2. A HAVING clause.

The following aggregate functions are available in SQL Server.

• SUM
• MAX
• MIN
• AVG
• COUNT
• APPROX_COUNT_DISTINCT
• CHECKSUM_AGG
• GROUPING
• GROUPING_ID
• VAR
• VARP
• COUNT_BIG
• STDEV
• STDEVP
• STRING_AGG

Note. To understand these aggregate functions create a table in SQL Server name as simple.

SUM function in SQL Server

This function disregards NULL values. It is an integrated function that takes a single parameter, a column, or a valid expression and outputs a single result that condenses the incoming data set. When the result set doesn't include any rows, it returns NULL. The WHERE, GROUP BY, ORDER BY, and HAVING clauses can also acquire the filtered output.

 SELECT SUM(Amount) AS TotalAmount FROM example

Result

MAX function in SQL Server

Aggregate functions include such like SQL Server's MAX() function. It is used to find the highest or largest value among a set of values for a certain column or expression. The WHERE, GROUP BY, ORDER BY, and HAVING clauses can also acquire the filtered output.

 SELECT MAX(Amount) AS MaxPrice FROM example

Result 

MIN function in SQL Server

Aggregate functions include the MIN() function in SQL Server. It determines the lowest or least value for a given column or expression. The WHERE, GROUP BY, ORDER BY, and HAVING clauses can also acquire the filtered output.

SELECT MIN(Amount) AS MinPrice FROM example

Result

AVG function in SQL Server 

A built-in function called AVG() is frequently used with the SELECT statement. The average value of an expression is determined with this function. This function ignores the values for NULL. The WHERE, GROUP BY, ORDER BY, and HAVING clauses can also acquire the filtered output.

SELECT AVG(Amount) AS average_price FROM example

Result

COUNT function in SQL Server

A SQL Server function called COUNT counts the number of rows in the table or result set supplied or returned by a SELECT statement. The COUNT function returns the quantity of non-null values included in the specified column or expression when given the column's name or expression as an argument. COUNT returns the overall number of rows in the table when used with the wildcard character asterisk (*).

SELECT COUNT(Amount) AS count_item FROM example

Result

APPROX_COUNT_DISTINCT function in SQL Server

A SQL Server function called APPROX_COUNT_DISTINCT calculates an approximation of the number of distinct values in a given column or expression. Based on a statistical sample of the data, APPROX_COUNT_DISTINCT employs a probabilistic approach to calculate the approximate number of unique values. When the precise number of unique values is not required, it is a quicker option to COUNT unique.

 SELECT APPROX_COUNT_DISTINCT(amount) AS APPROX_DISTINCT FROM example

Result

VAR function in SQL Server

A SQL Server function called VAR can determine the variance of a set of values in a given column or expression. It is a statistical function that assesses how variable or dispersed a dataset is. Variance is calculated as the sum of the squared deviations between each value and the mean divided by the total number of values less than 1. The VAR function receives the name of a column or expression as an argument and outputs the variance of the values in that column or expression.

SELECT  VAR(amount) AS var_value FROM example

Result

VARP function in SQL Server

A statistical function called VARP in SQL Server provides the variance of a collection of integers. The VARP function returns the values' variance in the column or expression that has been supplied. The variance measures how far apart the values are from the average value. The variance is aThe values are more evenly distributed when the variation is bigger, and they are more closely spaced when the variance is lower. 

SELECT  VARP(amount) AS varp_value FROM example

Result

COUNT_BIG function in SQL Server

The built-in COUNT_BIG function in SQL Server counts the number of rows in a given table or view. It is similar to the COUNT function but is used specifically for larger data sets when the output can be greater than the 2^31 - 1. maximum value of the standard COUNT function. Use COUNT_BIG rather than COUNT if you are working with massive data sets and need to count the number of rows to avoid integer overflow problems.

 SELECT  COUNT_BIG(amount) AS bigcount_value FROM example

Result

STDEV function in SQL Server

The built-in statistical function STDEV in SQL Server determines the standard deviation of a collection of numerical values in a given table column. It determines the degree of variance or dispersion among the column's data points.

 

 

SELECT  STDEV(amount) AS stdev_value FROM example

Result

STDEVP function in SQL Server

The built-in function STDEVP in SQL Server determines a population's standard deviation. It accepts a collection of numerical inputs and outputs the population's overall standard deviation. Remember that STDEVP delivers the population-wide standard deviation, not simply a sample. Instead, the STDEV function would be best to determine a sample's standard deviation.

SELECT  STDEVP(amount) AS stdevp_value FROM example

Result 

STRING_AGG function in SQL Server

The STRING_AGG String Function in SQL Server joins the string expressions and inserts a designated separator between them, remembering that it won't add a separator to the string's end. The WHERE, GROUP BY, ORDER BY, and HAVING clauses can also acquire the filtered output.

 SELECT STRING_AGG(amount,'-') AS col_row FROM example

Result

Conclusion

If you have any queries/suggestions on the article, please leave your questions and thoughts in the comment section below. Follow C# Corner to learn more new and amazing things about SQL Server or to explore more technologies.

Thanks for reading, and I hope you like it.