Introduction to Aggregate Functions
Explore the aggregation functions available in MySQL.
In SQL, we are used to seeing statements being applied to a table on a row-by-row basis. Doing so makes sense given the nature of SQL’s logical layer where data is stored in tables. Data retrieval using simple SELECT [...] FROM [...] WHERE [...]; statement is convenient this way. Our mental model fails when we want to operate on a set of rows. MySQL provides different constructs for these kinds of operations. Aggregate functions are among the most comprehensive and can be integrated seamlessly into our existing knowledge of SQL statements.
Operating on sets of values
Before looking at examples of aggregate functions, we want to rehearse our understanding of operating on sets of rows instead of rows alone. Let us consider the following table that consists of a single column with the data type SMALLINT populated with a number:
CREATE TABLE Aggregation(number SMALLINT NOT NULL);-- Populate table with exemplary numbers.INSERT INTO AggregationVALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);-- Inspect the populated table.TABLE Aggregation;
Using a SELECT statement without an aggregation function on Aggregation would perform an operation on a single row for all table ...