MySQL - Aggregate Functions

MySQL - Modern Aggregate Functions

In SQL, aggregate functions perform a calculation on a set of rows and return a single, summary value. These functions are fundamental for data analysis and reporting. They are commonly used with the GROUP BY clause to calculate aggregate values for various subgroups of rows. This tutorial covers the most common aggregate functions in MySQL, including modern additions and best practices.

Core Aggregate Functions

These are the foundational aggregate functions you will use in most of your queries:

Function	Description
COUNT()	Counts the number of rows. COUNT(*) counts all rows, COUNT(column) counts non-NULL values in a column, and COUNT(DISTINCT column) counts unique non-NULL values.
SUM()	Calculates the sum of a set of numeric values.
AVG()	Calculates the average of a set of numeric values.
MIN()	Returns the minimum value in a set of values.
MAX()	Returns the maximum value in a set of values.
GROUP_CONCAT()	Concatenates values from a group into a single string, with various options for ordering and separators.

Example: Using Core Functions

Imagine a products table with columns category, price, and stock_quantity. We can get a summary for each category:

SELECT
    category,
    COUNT(*) AS number_of_products,
    SUM(stock_quantity) AS total_stock,
    AVG(price) AS average_price,
    MAX(price) AS most_expensive
FROM
    products
GROUP BY
    category;

Statistical and Bitwise Functions

MySQL also provides functions for more advanced statistical analysis and bitwise operations.

Function	Description
STDDEV_POP()	Calculates the population standard deviation. STD() and STDDEV() are aliases for this function.
STDDEV_SAMP()	Calculates the sample standard deviation.
VAR_POP()	Calculates the population standard variance. VARIANCE() is an alias for this function.
VAR_SAMP()	Calculates the sample variance.
BIT_AND()	Calculates the bitwise AND of all values in a group.
BIT_OR()	Calculates the bitwise OR of all values in a group.
BIT_XOR()	Calculates the bitwise XOR of all values in a group.

Modern JSON Aggregation (MySQL 5.7+)

With the rise of JSON as a data interchange format, MySQL introduced powerful functions to aggregate data directly into JSON structures.

Function	Description
JSON_ARRAYAGG()	Aggregates values from a group into a single JSON array.
JSON_OBJECTAGG()	Aggregates key-value pairs from a group into a single JSON object.

Example: Aggregating into JSON

Using the same products table, we can group product names by category into a JSON array:

SELECT
    category,
    JSON_ARRAYAGG(product_name) AS products_in_category
FROM
    products
GROUP BY
    category;

Advanced Topic: Window Functions (MySQL 8.0+)

While not strictly aggregate functions, Window Functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, they do not collapse rows; they return a value for each row based on a ‘window’ of related rows defined by the OVER() clause. They are often used alongside aggregates for advanced reporting and analysis, such as calculating running totals, rankings, and moving averages.

Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), and aggregate functions used as window functions (e.g., SUM(...) OVER (...)). Exploring them is a great next step after mastering aggregate functions.