SQL Server provides many built-in functions that operate on a set of input values and result in a single output value. In this article, we are going to look at commonly used aggregate functions. SQL Aggregate functions can be easily understood through examples. Here is a simple example table that will be used through out this article to demonstrate SQL Aggregate functions.
Let’s consider the salary of the employees in an organization.
COUNT
This function returns the number of rows in a selection or group.
Syntax:
COUNT ( [ ALL | DISTINCT ] column_name ) or COUNT( *)
ALL – when specified, counts all items including duplicate values.
DISTINCT – when specified, ignores the duplicate items and returns the count of unique items.
* (asterisk) – when specified, returns the number of records in the table
For example, to return the number of employees working in the organization,
Let’s look at the difference between when the optional keywords ALL and DISTINCT are specified. For example, to get the number of departments in the organization,
From the above example, when the DISTINCT keyword is mentioned, the COUNT() returns the number of unique items in the department column. Now let’s see how the above example works if the DISTINCT keyword is replaced by the ALL keyword.
It counts all of the items in the department column including the duplicate items, thus it returns 8.
COUNT_BIG
This function works in the same way as COUNT () works. The COUNT_BIG differs only in the data type of the return value. It always returns the count in the bigint data type.
Syntax:
COUNT_BIG ( [ ALL | DISTINCT ] column_name ) or COUNT_BIG ( *)
SUM
This function returns the sum of all the values in the given numeric column.
Syntax:
SUM([ALL | DISTINCT] column_name)
For example, to calculate the total salary of all employees in the tblEmployees table:
Now, to calculate the total salary of each department:
MAX
This function returns the maximum value in the given numeric column.
Syntax:
MAX ( [ ALL | DISTINCT ] column_name )
For example, to find out the highest salary paid to the employees in the company:
Next, to evaluate the highest salary paid within each department of the company,
MIN
This function returns the minimum value in the given numeric column.
Syntax:
MIN ( [ ALL | DISTINCT ] column_name )
For example, to calculate the lowest salary paid in the company,
Now, to calculate the lowest salary paid within each department of the company,
AVG
This function returns the average of all the values in the given numeric column.
Syntax:
AVG ( [ ALL | DISTINCT ] column_name )
For example, to calculate the avearge salary of the employees in the company:
To calculate the average salary of each department in the company:
