Aggregation functions are subprograms in SQL that produce a single value when called. These functions are used to perform standard statistical calculations on attributes or fields within a table, such as calculating total values, averages, finding extreme values (smallest/largest), and counting the number of data records.
In simple terms, aggregation functions process multiple rows of data to produce one concise and informative output value.
1. Types of Aggregation Functions
There are five main aggregation functions commonly used in data processing:
- SUM This function is used to sum all data in a specific column. Note that the SUM function can only be applied to columns with numeric data types. Syntax: SELECT SUM(column_name) FROM table_name WHERE condition;
- COUNT This function is used to count the number of rows in a column. Unlike SUM, the COUNT function can work on both numeric and non-numeric (string/text) data types. Syntax: SELECT COUNT(column_name) FROM table_name WHERE condition;
- AVG (Average) This function is used to find the average value of a column. Like SUM, this function only works on numeric data types. Syntax: SELECT AVG(column_name) FROM table_name WHERE condition;
- MIN This function is used to display the smallest value from a column. The MIN function is flexible as it can work on both numeric and non-numeric data types. Syntax: SELECT MIN(column_name) FROM table_name WHERE condition;
- MAX The opposite of MIN, this function is used to display the largest value from a column. This function also supports numeric and non-numeric data types. Syntax: SELECT MAX(column_name) FROM table_name WHERE condition;
2. GROUP BY Clause
The GROUP BY clause is used to group data on one or several columns based on a desired expression. It works by collecting data records that have the same value into one group.
The use of this clause is closely related to aggregation functions, where we often want to see statistical results (such as totals or averages) for each specific category. Syntax: SELECT column_name FROM table_name GROUP BY column_name;
3. HAVING Clause
The HAVING clause has a similar function to the WHERE clause, which is to add conditions or filters to the query results. However, there are fundamental differences between the two:
- Replacement for WHERE: The WHERE clause cannot be used together with aggregation functions. Therefore, HAVING is used to filter data based on the results of these aggregation functions.
- Multi-row Operation: HAVING is used on multi-row operations (after data is grouped), while WHERE works at the single row level before data is grouped.
Syntax: SELECT column_name FROM table_name GROUP BY column_name HAVING condition;
