SQL Server Aggregate Functions

Aggregate functions in SQL Server are used to perform calculations on one or more values and return the result in a single value. In SQL Server, all aggregate functions are built-in functions that avoid NULL values except for COUNT(*). We mainly use these functions with the GROUP BY and HAVING clauses of the SELECT statements in the database query languages.

DBA generally used these functions for summarizing their data. When aggregate functions are invoked with a particular set of input values multiple times, they always return the same value. Therefore, they are also called deterministic functions. It is noted that the aggregate functions cannot be nested, and the expression cannot be a subquery.

Syntax:

The following are the syntax to use aggregate functions in MySQL:

In this syntax, we can see the following parameters:

aggregate_function_name: It indicates the name of the aggregate function that we want to use.

DISTINCT | ALL: The DISTINCT modifier is used when we want to consider the distinct values in the calculation. The ALL modifiers are used when we want to calculate all values, including duplicates. If we do specify any modifier, all aggregate functions use the ALL modifier by default.

exp: It indicates the table's columns or an expression containing multiple columns with arithmetic operators.

SQL Server provides various aggregate functions, and the most commonly used aggregate functions are shown in the below table:

This table shows some other aggregate functions used in SQL Server:

Why we use aggregate functions?

The aggregate functions are mainly used to produce the summarized data in economics and finance to represent the economic health or stock and sector performance. In the context of business, different organization levels need different information, such as top levels managers interested in knowing whole figures and not the individual details.

Aggregate Functions Example

Let us understand how the most commonly used aggregate functions work on the database. Here we will first create an employee table for the demonstration of all aggregate functions.

Execute the below statement to create an employee table:

Next, we will insert some data into this table as follows:

We can see the table records using the SELECT statement:

COUNT() Function

This function returns the total number of rows, including NULL values in the given expression. It can also count all records based on a specified condition and returns zero if it does not find any matching records. It can work with both numeric and non-numeric data types.

Example

The below example uses the COUNT() function and returns the total number of employees data stored in the employee table:

Output:

To read more information, click here.

SUM() Function

This function calculates the total summation of NON-NULL values in the given set. It returns NULL if the result set does not have any records. The SUM function can only work with the numeric data type.

Example

The below example uses the SUM function and calculates the total summed up salary of all employees stored in the employee table:

Output:

After execution, we can see the total salary of all employees in the table:

To read more information, click here.

AVG() Function

This function calculates the average of NON-NULL values specified in the column. The AVG function can only work with the numeric data type.

Example

The below example uses the AVG function and calculates the average salary of employees stored in the employee table:

Output:

After execution, we can see that the average salary of employees in the table:

To read more information, click here.

MIN() Function

This function gives the minimum (lowest) value of the specified column. It also works with numeric data types only.

Example

The below example uses the MIN function and returns the lowest salary of an employee stored in the employee table:

Output:

Here we can see that the minimum salary of an employee available in the table:

To read more information, click here.

MAX() Function

This function gives the maximum (highest) value of the specified column. It also works with numeric data types only.

Example

The below example uses the MAX function and returns the highest salary of employees stored in the employee table:

Output:

Here we can see that the minimum salary of an employee available in the table:

To read more information, click here.

Aggregate Functions & JOINs

SQL Server can enable us to use the aggregate function to retrieve resultant data from more than one table. To understand this concept, we will create another table named "emp_address" that stores the address of each employee. Here is the query to create a table:

We can verify the table as follows:

Suppose we want to calculate the total number of employee and their addresses from two different tables. We can do this by using the below statement:

We will get the resultant output:

In this article, we have learned about the popular aggregate functions of SQL Server and how we can use them to calculate aggregates.