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:
aggregate_function_name(DISTINCT | ALL exp) 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:
CREATE TABLE employee(
name varchar(45) NOT NULL,
occupation varchar(35) NOT NULL,
working_date date,
working_hours varchar(10),
salary INT
);Next, we will insert some data into this table as follows:
INSERT INTO employee VALUES
('Jolly Evans', 'HR', '2020-10-04', 9, 25000),
('Brayden Simmons', 'Engineer', '2020-10-04', 12, 65000),
('Rose Huges', 'Writer', '2020-10-04', 13, 35000),
('Laura Paul', 'Manager', '2020-10-04', 10, 45000),
('Diego Simmons', 'Teacher', '2020-10-04', 12, 30000),
('Antonio Bennet', 'Writer', '2020-10-04', 13, 35000);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:
SELECT COUNT(*) AS total_employees FROM employee; 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:
SELECT SUM(salary) AS total_salary FROM employee; 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
SELECT AVG(salary) AS "Average Salary" FROM employee; 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:
SELECT MIN(salary) AS "Lowest Salary" FROM employee; 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:
SELECT MAX(salary) AS "Highest Salary" FROM employee; 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:
CREATE TABLE emp_address(
name varchar(45),
cellphone varchar(25),
address varchar(90),
city varchar(35)
);
INSERT INTO emp_address(name, cellphone, address, city) VALUES
('Jolly Evans', '334369253659', '777 Brockton Avenue, Abington MA 2351', 'California'),
('Brayden Simmons', '359436598356', '3849 Route 31, Clay NY 12041', 'New York'),
('Rose Huges', '768055634859', '345 East Meighan Blvd, Gadsden AL 359', 'Alaska'),
('Laura Paul', '948563945327', '301 RT 9W, Glenmont NY 12077', 'New York'),
('Diego Simmons', '465676423435', '501 Memorial Dr, Chicopee MA 1030', 'California'),
('Antonio Bennet', '506705670323', '317 Russell St, Hadley MA 1415', 'California');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:
SELECT COUNT(employee.name) AS Names,
COUNT(emp_address.city) AS addresses FROM employee
INNER JOIN emp_address ON employee.name = emp_address.name;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.
Leave a Reply