Javatpoint Logo
Javatpoint Logo

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:

Aggregate Function Descriptions
COUNT() This function counts the number of elements or rows, including NULL values in the defined set.
SUM() This function calculates the total sum of all NON-NULL values in the given set.
AVG() This function performs a calculation on NON-NULL values to get the average of them in a defined set.
MIN() This function returns the minimum (lowest) value in a set.
MAX() This function returns the maximum (highest) value in a set.

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

Aggregate Function Descriptions
CHECKSUM_AGG It calculates the checksum of the values in a defined set.
COUNT_BIG() It counts the number of elements, including NULL values in a defined set. This function is the same as the COUNT() function, but it returns a BIG INT data type, whereas COUNT returns an INT data type.
STDEV() It calculates the statistical standard deviation of each value in the defined expression on the basis of a sample data population.
STDEVP() It calculates the standard deviation for each value in the given expression on the basis of an entire data population.
VAR() It calculates the statistical variance of each element in the defined expression on the basis of a sample data population.
VARP() It calculates the statistical variance of each element in the defined expression on the basis of an entire data population.
GROUPING() It signifies whether or not a GROUP BY lists specified column expression is aggregated. If the result set shows 1, it means the result set is aggregated and, if not, returns 0.
GROUPING_ID() It is used to computes the level of grouping.

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:

SQL Server Aggregate Functions

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:

SQL Server Aggregate Functions

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:

SQL Server Aggregate Functions

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:

SQL Server Aggregate Functions

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:

SQL Server Aggregate Functions

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:

SQL Server Aggregate Functions

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:

SQL Server Aggregate Functions

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:

SQL Server Aggregate Functions

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






Youtube For Videos Join Our Youtube Channel: Join Now

Feedback

  • Send your Feedback to feedback@javatpoint.com

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA