You must have heard of functions like AVG, Count etc in SQL or SQL server, but many of us still don't know how to use Aggregate functions in SQL, so in this guide, I am going to explain about all of the Aggregate functions available in SQL server with an example.

What are Aggregate Functions in SQL?

An aggregate function is a function where the values of multiple rows are grouped together to form a single value of more significant meaning or measurements such as a set or a list.

So, in other words, Aggregate functions perform a calculation on a set of values and return a single value.

The aggregate functions – COUNT, SUM, AVG, MAX, MIN  – don't handle NULL in the same way as ordinary functions and operators. Instead of returning NULL as soon as a NULL operand is encountered, they only take non-NULL fields into consideration while computing the outcome

SQL Aggregate functions are among the ones most widely used in reporting & data joining scenarios. Here are the few SQL Aggregate functions which I am going to explain today

  1. COUNT ()  :  returns the total number of values in a given column
  2. SUM ()  : returns the sum of the numeric values in a given column
  3. AVG  () : returns the average value of a given column
  4. MIN() : returns the smallest value in a given column
  5. MAX () : returns the largest value in a given column

Let's discuss each of them with an example. I will be using a database AdventureWorks on my local SQL server database, you can download the complete database using https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks if you have SQL server, else you can download SQL server various version.

Let's create a new table on which using which I will be explaining our example:

USE [AdventureWorks2012]
GO
 
SELECT
    P.BusinessEntityID AS EmpID, P.Title, P.FirstName, P.MiddleName, P.LastName, 
    E.Gender, E.MaritalStatus, E.BirthDate AS DOB, E.HireDate AS DOJ, E.JobTitle, 
    D.Name AS DeptName, S.Rate
INTO dbo.Employee -- Create a new table here and load query data
FROM [Person].[Person] P
INNER JOIN [HumanResources].[Employee] E 
        ON E.BusinessEntityID = P.BusinessEntityID
CROSS APPLY (
    SELECT TOP 1 DepartmentID
    FROM [HumanResources].[EmployeeDepartmentHistory] DH 
    WHERE DH.BusinessEntityID = E.BusinessEntityID 
    ORDER BY StartDate DESC) EDH
INNER JOIN [HumanResources].[Department] D 
        ON D.DepartmentID = EDH.DepartmentID
INNER JOIN [HumanResources].[EmployeePayHistory] S 
        ON S.BusinessEntityID = P.BusinessEntityID
 
SELECT * FROM dbo.Employee
GO

Output will be a list of Employee's

employee-table-output-sql-aggregate-functions-sum

COUNT() function

Count function is used to return the number of rows in a table or a group and returns an INT (integer).

Syntax

-- Syntax for SQL Server and Azure SQL Database  

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )   
    [ OVER (   
        [ partition_by_clause ]   
        [ order_by_clause ]   
        [ ROW_or_RANGE_clause ]  
    ) ]  

Let's try to use the COUNT function in the above-created table in SQL server with different scenario's

-- To get count of all reords in a table:
select COUNT(*) from dbo.Employee -- 316
 
-- Within Group: get count of records having Gender = 'M':
select COUNT(*) from dbo.Employee WHERE Gender = 'M' -- 228
 
 -- Using ColumnName to COUNT:
select COUNT(FirstName) from dbo.Employee -- 316

-- Using DISTINCT with COUNT:
select COUNT(DISTINCT FirstName) from dbo.Employee -- 224

-- Using Count to provide output column a name
select COUNT(DISTINCT FirstName) as Total from dbo.Employee  -- 224

I have commented out each of the cases so you can understand the difference, let's take a look on the output, when it is executed in SQL server

count-aggregate-function-output-min.png

The Last example which I have highlighted using red border show's the output as custom named, column, so basically, you can provide your own name to the output of COUNT and can use it in other sub-query also.

Using COUNT() function with GROUP BY

-- Get count of Employees in every Department:
select
    DeptName, 
    COUNT(*) as EmpCount
from dbo.Employee
GROUP BY DeptName

count-group-by-min.png

AVG Aggregate function

Avg function returns the average of the values in a group. Null values are ignored.

AVG () computes the average of a set of values by dividing the sum of those values by the count of nonnull values. If the sum exceeds the maximum value for the data type of the return value an error will be returned.

AVG is a deterministic function when used without the OVER and ORDER BY clauses. It is non-deterministic when specified with the OVER and ORDER BY clauses.

Syntax

AVG ( [ ALL | DISTINCT ] expression )  
   OVER ( [ partition_by_clause ] order_by_clause )

Example for using AVG with Group By, the following example produces summary values for each sale territory in the AdventureWorks2012 database. The summary lists the average bonus received by the sales people in each territory.

SELECT TerritoryID, AVG(Bonus)as 'Average bonus'  
FROM Sales.SalesPerson  
GROUP BY TerritoryID;  
GO  

Output:

output-avg-function-min.png

MIN() , MAX() functions

MIN() & MAX() functions are used to returns the Minimum & Maximum values from a table or a group, respectively.

Syntax for Min

MIN ( [ ALL | DISTINCT ] expression )  
   OVER ( [ partition_by_clause ] order_by_clause )  

Syntax for Max

MAX ( [ ALL | DISTINCT ] expression )  
   OVER ( [ partition_by_clause ] order_by_clause )     

MAX & MIN ignores any null values.

For character columns, MAX finds the highest value in the collating sequence while MIN finds the value that is lowest in the sort sequence.

Let's take a look at some example, following example returns the lowest (minimum) tax rate. The example uses the AdventureWorks2012 database

SELECT MIN(TaxRate)  
FROM Sales.SalesTaxRate;  
GO  

Output

5.00

For MAX

SELECT MAX(TaxRate)  
FROM Sales.SalesTaxRate;  
GO 

Output

19.60

Using Min, Max, AVG, COUNT all together

SELECT DISTINCT Name  
       , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary  
       , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary  
       , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary  
       ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept  
FROM HumanResources.EmployeePayHistory AS eph  
JOIN HumanResources.EmployeeDepartmentHistory AS edh  
     ON eph.BusinessEntityID = edh.BusinessEntityID  
JOIN HumanResources.Department AS d  
 ON d.DepartmentID = edh.DepartmentID  
WHERE edh.EndDate IS NULL  
ORDER BY Name;  

min-max-avg-count-all-aggregate-functions-min.png

The above example uses MIN, MAX, AVG, and COUNT functions with the OVER clause to provide aggregated values for each department in the HumanResources.Department table in the AdventureWorks2012 database. 

SUM() function

It returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.

SUM ( [ ALL | DISTINCT ] expression )  
   OVER ( [ partition_by_clause ] order_by_clause )   

For example, the below query shows, using the SUM function to return summary data in the AdventureWorks2012 database.  

SELECT Color, SUM(ListPrice), SUM(StandardCost)  
FROM Production.Product  
WHERE Color IS NOT NULL   
    AND ListPrice != 0.00   
    AND Name LIKE 'Mountain%'  
GROUP BY Color  
ORDER BY Color;  
GO  

Output

Sum-aggregate-function-min.png

There are few more in SQL server like COUNT_BIG, CHECKSUM_AGG, STDEV, STDEVP, GROUPING, GROUPING_ID, VAR, VARP. You can check them here: https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql but we have discussed all the widely used Aggregate functions.