Introduction:
In this article,i am going to explain about how to effectively use/utilize the sql-server build in
aggregate functions in t-sql code.

Main:
Aggregate Functions

Aggregate functions:- are used to perform a calculation on one or more values, resulting in a single value.
An example of a commonly used aggregate function is SUM, which is used to return the total value of a set
of numeric values.

AVG:-The AVG aggregate function calculates the average of non-NULL values in a group.

CHECKSUM_AGG:-The CHECKSUM_AGG function returns a checksum value based on a group of rows, allowing you to
potentially track changes to a table. For example, adding a new row or changing the value of a column that
is being aggregated will usually result in a new checksum integer value. The reason why I say “usually” is
that there is a possibility that the checksum value does not change even if values are modified.

COUNT:-The COUNT aggregate function returns an integer data type showing the count of rows in a group.

COUNT_BIG:-The COUNT_BIG function works the same as COUNT, only it returns a bigint data type value.

GROUPING:-The GROUPING function returns 1 (True)or 0 (False) depending on whether a NULL value is due
to a CUBE, ROLLUP, or GROUPING SETS operation. If False, the column expression NULL value is from the
natural data. See Chapter 1′s recipe “Revealing Rows Generated by GROUPING.”

MAX:-The MAX aggregate function returns the highest value in a set of non-NULL values.

MIN:-The MIN aggregate function returns the lowest value in a group of non-NULL values.

SUM:-The SUM aggregate function returns the summation of all non-NULL values in an expression.

STDEV:-The STDEV function returns the standard deviation of all values provided in the expression based
on a sample of the data population.

STDEVP:-The STDEVP function also returns the standard deviation for all values in the provided expression,
only it evaluates the entire data population.

VAR:-The VAR function returns the statistical variance of values in an expression based on a sample of the
provided population.

VARP:-The VARP function also returns the variance of the provided values for the entire data population of
the expression.

Returning the Average of Values:-
The AVG aggregate function calculates the average of non-NULL values in a group. For example:

-- Average Product Review by Product SELECT ProductID, AVG(Rating) AvgRating FROM Production.ProductReview GROUP BY ProductID

Returning ROW COUNT:-

SELECT Shelf, COUNT(ProductID) ProductCount FROM Production.ProductInventory GROUP BY Shelf ORDER BY Shelf

Min and Max:

SELECT MIN(Rating) MinRating, MAX(Rating) MaxRating FROM Production.ProductReview

Returning Sum of the values,

SELECT AccountNumber, SUM(TotalDue) TotalDueBySalesOrderID FROM Sales.SalesOrderHeader GROUP BY AccountNumber ORDER BY AccountNumber

Using Statistical Aggregate Functions

In this recipe, I’ll demonstrate using the statistical functions VAR, VARP, STDEV, and STDEVP.

The VAR function returns the statistical variance of values in an expression based on a sample of the provided population
(the VARP function also returns the variance of the provided values for the entire data population of the expression).

This first example returns the statistical variance of the TaxAmt value for all rows in the Sales.SalesOrderHeader table:

SELECT VAR(TaxAmt) Variance_Sample, VARP(TaxAmt) Variance_EntirePopulation FROM Sales.SalesOrderHeader

Conclusion:
Hope this helps,
Happy coding.