SQL Aggregate Functions | What are Aggregate functions in SQL
In these articles, you will learn about the SQL Aggregate functions such as COUNT(), AVG(), SUM(), MIN(), MAX().
The SQL Aggregate functions calculate on a set of values and return a single value, such as for example, the SUM function SUM() takes a list of values and returns the SUM of values.
Except for COUNT aggregate functions SQL ignore null values, the SQL Aggregate functions are often used with the GROUP BY clause of the SELECT statement.
The GROUP BY clause divides the result set into groups of values and the aggregate function SQL returns a single value for each group.
All aggregate functions in SQL are deterministic, therefore, in other words, the aggregate function returns the same value each time that they are called, when called with a specific set of input values.
The OVER clause may follow all SQL aggregate functions, except the STRING_AGG, GROUPING_ID, or GROUPING functions.
There are the given below, the following illustrates how the aggregate function is used with the GROUP BY clause:
FROM table
GROUP BY c1;
COUNT() – the COUNT() function returns the number of items in a set.
AVG() – The AVG() function returns the average of a set.
SUM() – The SUM() function returns the sum of all or distinct values in a set
MIN() – The MIN() function returns the minimum value in a set
MAX() – The MAX() function returns the maximum value in a set.
Furthermore, Use aggregate functions as expressions only in the following situations:
The select list of a SELECT statement (either an outer query or subquery).
A HAVING clause. Transact-SQL provides the following SQL aggregate functions:
Transact-SQL provides the following aggregate functions:
| Functions | Description |
|---|---|
| APPROX_COUNT_DISTINCT | APPROX_COUNT_DISTINCT( expression ) function evaluates an expression for each row in a group and it returns the approximate number of unique non-null values in a group. Therefore mainly, this function is designed to provide aggregations across big data sets where responsiveness is more critical than absolute precision. |
| AVG | The AVG() function in SQL returns the average value of a numeric column and NULL values are ignored |
| CHECKSUM_AGG | The CHECKSUM_AGG() SQL Server function is an aggregate function that returns the checksum of the values in a set. ALL instructs the function to return the checksum of all values including duplicates and DISTINCT forces the function to calculate the checksum of distinct values. |
| COUNT | The COUNT() SQL function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. furthermore, It sets the number of rows or non NULL column values. The COUNT() returns 0 if there were no matching rows |
| COUNT_BIG | The SQL COUNT_BIG (DISTINCT expression) Function evaluates the expression for each row in a group and returns the number of unique and nonnull values. The COUNT_BIGSQL function is a deterministic function when used without the OVER and ORDER BY clauses |
| SUM | The SQL SUM() is an Aggregate function that returns the total sum of a numeric column. |
| GROUPING | The GROUP BY statement is used for the purpose groups rows that have the same values into summary rows, like “find the number of customers in each country”. therefore, The GROUP BY statement is often used with aggregate functions (SUM, AVG, COUNT, MAX, MIN) to group the result-set by one or more Related columns. |
| GROUPING_ID | Therefore in SQL GROUPING_ID, the column list for the GROUPING_ID function has to match a column list in the GROUP BY clause |
| VAR | VAR is a deterministic function and it used without the ORDER BY and OVER clauses. It is non-deterministic when specified with the ORDER BY and OVER clauses. |
| MAX | The SQL MAX() Functions return the largest value of the selected column. |
| MIN | The SQL MIN() Functions return the smallest value of the selected column. |
| STDEV | The STDEV function in SQL is the Aggregate Function and used to calculate the standard deviation of the available records. furthermore, The SQL STDEV function only executes on the numerical data and it disregards NULL values. thus we can use STDEV as an independent SQL aggregate function and as an analytical function |
| STDEVP | The SQL STDEVP function is used on all items in a SELECT statement, each value in the result set is included in the calculation. the STDEVP can be used with numeric columns only. the Null values are ignored. Therefore, the STDEVP is a deterministic function when used without the OVER and ORDER BY clauses. |
| STRING_AGG | The STRING_AGG() SQL Function is an aggregate function that concatenates rows of strings into a single string and separated by a specified separator. therefore, It does not add the separator at the end of the result string |
Demo Table: DoctorDetails
| Reg_Id | DoctorName | HospitalName | City | Salary |
|---|---|---|---|---|
| 101 | Aarav Adriano | Apollo Hospitals | Mumbai | 45000 |
| 102 | Alexei Alvaro | Billroth Hospitals | Delhi | 42000 |
| 201 | Angus Ara | Care Hospitals | Noida | 41000 |
| 104 | Armando Aurelio | Council of Christian Hospitals | Kolkata | 45000 |
| 105 | Bruno Cillian | Dr. Agarwal Eye Hospital | Chennai | 40000 |
| 221 | Didier Dimitri | Command Hospital | Delhi | 48000 |
| 107 | Eduardo Eissa | Hinduja Healthcare Limited | Nagpur | 46000 |
| 108 | Fabrizio Florian | LifeSpring Hospitals | Hyderabad | 50000 |
| 222 | Gaston Guillaume | Vasan Healthcare | Jaipur | 41000 |
| 110 | Helio Janos | LifeSpring Hospitals | Muradabad | 50000 |
| 111 | Adeline Anais | Wockhardt Hospitals | Agra | 47000 |
| 272 | 2Anne Marie Angelina | Yashoda Hospitals | Sultanpur | 51000 |
| 235 | Christine Claire | Sahyadri Hospital | Jhansi | 53000 |
| 236 | Germaine Henriette | Regional Cancer Centre | Gorakhpur | 55000 |
| 301 | Lucienne Lucile | Sahyadri Hospital | Bangal | 54000 |
COUNT(): SQL Aggregate Functions
The COUNT() aggregate functions return the total number of rows that matched the specified criteria. The following given below syntax shows the COUNT() function:
Example:
1. For instance, let’s to find the total number of Record in given above demo table, the given query can be used.
|
1 2 |
SELECT COUNT(*) FROM DoctorDetails |
2. To find the total number of Doctor who have salary 45000.
|
1 2 3 |
SELECT COUNT(*) FROM DoctorDetails WHERE Salary < 45000; |
AVG(): SQL Aggregate Functions
The AVG() aggregate function SQL returns the average value in a column. There are the given below following illustrates the syntax of the AVG() function:
The ALL keyword instructs the AVG() function to calculate the average of all values but while the DISTINCT keyword forces the function to operate on distinct values only. By default, the ALL option is used.
Example:
1. To find the average salary for the Doctor who have the salary less than 45000
|
1 2 3 |
SELECT AVG(Salary) FROM DoctorDetails WHERE Salary < 45000; |
SUM(): SQL Aggregate Functions
The SUM() aggregate function in SQL takes the name of a column as an argument and returns the sum of all the values in that column, THEREFORE, The sum() function has the following syntax:
Example:
1. To find the SUM of Doctors salary in the given above demo table.
|
1 2 |
SELECT SUM(Salary) FROM DoctorDetails; |
MIN(): SQL Aggregate Functions
Therefore, The MIN() aggregate function in SQL returns the always smallest value in a column, The MIN() function has the following syntax:
Example:
1. Find the smallest value of the Salary column from the table named DoctorDetails.
|
1 2 |
SELECT MIN(Salary) FROM DoctorDetails; |
MAX(): SQL Aggregate Functions
The MAX() aggregate function in SQL takes the name of a column as an argument and returns the always largest value in the column. therefore, the MAX() function has the following syntax:
Example:
1. Find the Largest value of the Salary column from the table named DoctorDetails.
|
1 2 |
SELECT MAX(Salary) FROM DoctorDetails; |
Column References
Therefore, the ORDER BY and GROUP BY clauses can reference the selected columns by number in which they appear in the SELECT statement. furthermore, the given example query will count the number of Shows per rating, and will:
|
1 2 3 4 5 |
SELECT COUNT(*) AS 'total_shows', rating FROM Shows GROUP BY 3 ORDER BY 2; |
GROUP BY column 3 (rating)
ORDER BY column 2 (total_shows)
GROUP BY Clause
The GROUP BY clause in SQL will group the records in a result set by the identical values in one or more columns. furthermore, It is often used in the combination with aggregate functions in SQL to query information of similar records. therefore, The GROUP BY clause in SQL can come after FROM or WHERE clause, but it will come before any ORDER BY or LIMIT clause.
The given query will count the number of shows per rating.
|
1 2 3 4 |
SELECT rating, COUNT(*) FROM shows GROUP BY rating; |
HAVING Clause
The HAVING clause in SQL is used to further filter the result set groups provided by the GROUP BY clause. therefore, HAVING is often used with SQL aggregate functions to filter the result set groups which is based on an aggregate property. furthermore, The given query will select only the records (rows) from only years where more than 10 Shows were released per year.
|
1 2 3 4 5 |
SELECT year, COUNT(*) FROM shows GROUP BY year HAVING COUNT(*) > 10; |
ROUND() Function
The ROUND() function in SQL will round a number value to the specified number of places. furthermore, It takes two arguments: a number, and a number of decimal places. It can be combined with other aggregate functions SQL, therefore, as shown in the given query. This query will calculate the average rating of Shows from 2012, rounding to 2 decimal places.
|
1 2 3 4 |
SELECT year, ROUND(AVG(rating), 2) FROM shows WHERE year = 2012; |
Conclusion:
In this Article, Therefore, we have introduced you to the SQL aggregate functions including the most commonly used functions: AVG, COUNT, SUM, MIN, and MAX. I hope you will enjoy it.
About Author
