Aggregate Functions and Data Grouping in SQL

An aggregate function is a subprogram in SQL that returns a single value when called. This function is used to perform standard statistical calculations on attributes or fields in a table, such as calculating total values, averages, finding extreme values (minimum/maximum), and counting the number of data records.

In simple terms, aggregate functions process many rows of data to produce a single concise and informative output value.

1. Types of Aggregate Functions

There are five main aggregate 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 with 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 of a column. The MIN function is flexible because it can work with numeric and non-numeric data types. Syntax: SELECT MIN(column_name) FROM table_name WHERE condition;
• MAX Opposite of MIN, this function is used to display the largest value of 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 more columns based on a desired expression. It works by collecting data records that have the same value into a single group.

The use of this clause is closely related to aggregate 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 query results. However, there is a fundamental difference between the two:

1. Replacement for WHERE: The WHERE clause cannot be used together with aggregate functions. Therefore, HAVING is used to filter data based on the results of aggregate functions.
2. Multi-row Operation: HAVING is used on multi-row operations (after data is grouped), while WHERE operates at the single-row level before data grouping.

Syntax: SELECT column_name FROM table_name GROUP BY column_name HAVING condition;