This cheatsheet covers the common aggregation functions in MySQL, their usage, and examples.
- Basic Aggregation Functions
- COUNT()
- SUM()
- AVG()
- MAX()
- MIN()
- Advanced Aggregation Functions
- GROUP_CONCAT()
- DISTINCT with Aggregation
- Using Aggregation Functions with GROUP BY
- HAVING Clause
- Window Functions
- Conditional Aggregation
Basic Aggregation Functions
COUNT()
Counts the number of rows or non-NULL values.
-- Count all rows
SELECT COUNT(*) FROM table_name;
-- Count non-NULL values in a specific column
SELECT COUNT(column_name) FROM table_name;
-- Count distinct values
SELECT COUNT(DISTINCT column_name) FROM table_name;
-- Example: Count total orders and unique customers
SELECT COUNT(*) AS total_orders, COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;
SUM()
Calculates the sum of a set of values.
SELECT SUM(column_name) FROM table_name;
-- Example: Calculate total revenue
SELECT SUM(order_total) AS total_revenue FROM orders;
AVG()
Calculates the average value of a set of values.
SELECT AVG(column_name) FROM table_name;
-- Example: Calculate average order value
SELECT AVG(order_total) AS average_order_value FROM orders;
MAX()
Returns the maximum value in a set of values.
SELECT MAX(column_name) FROM table_name;
-- Example: Find the most expensive product
SELECT MAX(price) AS highest_price FROM products;
MIN()
Returns the minimum value in a set of values.
SELECT MIN(column_name) FROM table_name;
-- Example: Find the least expensive product
SELECT MIN(price) AS lowest_price FROM products;
Advanced Aggregation Functions
GROUP_CONCAT()
Concatenates a set of strings and returns the result as a string.
SELECT GROUP_CONCAT(column_name) FROM table_name;
-- With separator and ordering
SELECT GROUP_CONCAT(column_name ORDER BY other_column SEPARATOR ', ') FROM table_name;
-- Example: List all product names for each category
SELECT category_id, GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ') AS products
FROM products
GROUP BY category_id;
DISTINCT with Aggregation
Use DISTINCT inside aggregation functions to consider only unique values.
SELECT COUNT(DISTINCT column_name) FROM table_name;
-- Example: Count unique products ordered
SELECT COUNT(DISTINCT product_id) AS unique_products_ordered FROM order_items;
Using Aggregation Functions with GROUP BY
GROUP BY is often used with aggregation functions to perform calculations on groups of rows.
SELECT column1, AGG_FUNCTION(column2)
FROM table_name
GROUP BY column1;
-- Example: Calculate total sales for each product
SELECT product_id, SUM(quantity * price) AS total_sales
FROM order_items
GROUP BY product_id;
HAVING Clause
Use HAVING to filter the results of GROUP BY based on the result of an aggregate function.
SELECT column1, AGG_FUNCTION(column2)
FROM table_name
GROUP BY column1
HAVING AGG_FUNCTION(column2) > value;
-- Example: Find products with total sales over 1000
SELECT product_id, SUM(quantity * price) AS total_sales
FROM order_items
GROUP BY product_id
HAVING total_sales > 1000;
Window Functions
MySQL 8.0+ supports window functions, which perform calculations across a set of rows related to the current row.
SELECT column1, column2,
AGG_FUNCTION(column3) OVER (PARTITION BY column1 ORDER BY column2) AS window_result
FROM table_name;
-- Example: Calculate running total of sales for each product
SELECT product_id, order_date, sales,
SUM(sales) OVER (PARTITION BY product_id ORDER BY order_date) AS running_total
FROM product_sales;
Conditional Aggregation
Use CASE statements inside aggregation functions for conditional aggregation.
SELECT
SUM(CASE WHEN condition THEN value ELSE 0 END) AS conditional_sum
FROM table_name;
-- Example: Count orders by status
SELECT
COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_orders,
COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_orders
FROM orders;