Aggregate Functions in SQL
Aggregate functions in SQL are used to perform calculations on a set of values and return a single value. These functions operate on a group of values and return a single result. Some of the commonly used aggregate functions in SQL are:
- COUNT function: The COUNT function is used to count the number of rows in a table. It takes a column name or an asterisk (*) as a parameter.
Syntax: SELECT COUNT(column_name) FROM table_name;
Example: SELECT COUNT(*) FROM employees;
- SUM function: The SUM function is used to calculate the sum of all the values in a column.
Syntax: SELECT SUM(column_name) FROM table_name;
Example: SELECT SUM(salary) FROM employees;
- AVG function: The AVG function is used to calculate the average of all the values in a column.
Syntax: SELECT AVG(column_name) FROM table_name;
Example: SELECT AVG(salary) FROM employees;
- MAX function: The MAX function is used to find the highest value in a column.
Syntax: SELECT MAX(column_name) FROM table_name;
Example: SELECT MAX(salary) FROM employees;
- MIN function: The MIN function is used to find the lowest value in a column.
Syntax: SELECT MIN(column_name) FROM table_name;
Example: SELECT MIN(salary) FROM employees;
Aggregate functions can also be used with the GROUP BY clause to group the data by one or more columns. This is useful when you want to perform aggregate calculations on subsets of data.
Syntax: SELECT column_name, COUNT(*), SUM(salary) FROM employees GROUP BY column_name;
Example: SELECT department, COUNT(*), SUM(salary) FROM employees GROUP BY department;
In conclusion, aggregate functions in SQL are powerful tools for performing calculations on large sets of data. They allow you to calculate summary statistics such as counts, averages, and sums, and can be used to group data to perform more complex calculations.
OR
SQL aggregate functions such as SUM, AVG, MAX, MIN, COUNT etc. produce a single value for the entire group of table entries. Aggregate or column functions can also be used along with SELECT command.
SUM
Sum function calculates the arithmetic SUM of all selected values of a given column. For example, to find sum of fee paid by students from STUDENTS TABLEÂ we would like to use following commandÂ
SELECT SUM(FEE) FROM STUDENTS;Â Â
Here fee is a column name in the students table
AVG (AVERAGE)
Using the AVG command we can calculate average of all the selected values of a given column or field. Here one thing is to note that AVG(DISTINCT) eliminates duplicate field values before calculation.Â
Using same example here we will find average fee paid by using following statement
SELECT AVG(FEE) FROM STUDENT;MAX
MAX function calculates the largest of all selected values of given column. To check the maximum value in fee column we use following commandÂ
SELECT MAX(FEE) FROM STUDENTS; Â
MIN
MIN function is used to calculate lowest value in a specified field. Taking same example here we will find minimum fee paid by any student. By this statement
SELECT MIN(FEE) FORM STUDENTS;COUNTÂ
This function counts the number of rows present in the output table. The function COUNT with the star(*) counts the number of rows in the resulting table. Whereas COUNT(DISTINCT) eliminates duplicate fields before counting them in the output table. For example to count the total number of students present in the students table we use following statement:
SELECT COUNT(*)FROM STUDENTS;
