Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:
| Function | Description |
|---|---|
| AVG() | Returns the average value |
| COUNT() | Returns the number of rows |
| MAX() | Returns the largest value |
| MIN() | Returns the smallest value |
| SUM() | Returns the sum |
AVG() Function
The AVG() function returns the average value of a numeric column.
SQL AVG() Syntax:
SELECT AVG(column_name) FROM table_name
Now we want to find the average value of the “salary” fields.
We use the following SQL statement:
SELECT AVG(salary) from employee;
COUNT() Function
The COUNT() function returns the number of rows that matches a specified criteria.
SQL COUNT(column_name) Syntax
SELECT COUNT(column_name) FROM table_name
SQL COUNT(*) Syntax
The COUNT(*) function returns the number of records in a table:
SELECT COUNT(*) FROM table_name
SQL COUNT(DISTINCT column_name) Syntax
The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:
SELECT COUNT(DISTINCT column_name) FROM table_name
MAX() Function
The MAX() function returns the largest value of the selected column.
SQL MAX() Syntax
SELECT MAX(column_name) FROM table_name
MIN() Function
The MIN() function returns the smallest value of the selected column.
SQL MIN() Syntax
SELECT MIN(column_name) FROM table_name
SUM() Function
The SUM() function returns the total sum of a numeric column.
SQL SUM() Syntax
SELECT SUM(column_name) FROM table_name
The GROUP BY clause permits a WITH ROLLUP modifier that causes extra rows to be added to the summary output. These rows represent higher-level (or super-aggregate) summary operations. ROLLUP thus enables you to answer questions at multiple levels of analysis with a single query.
Suppose if we want to find the number of male and female employees, we can use the COUNT() on ‘ssn’ with ‘gender’ column specified in GROUP BY clause. This will count the number of employees based on ‘gender’.
But we also want to find the total number of employees as well in the same query. Here is where, you can use the GROUP BY modifier, WITH ROLLUP as shown below.
The last row with ‘NULL’ value on ‘gender’ column gives the total number of employees.
