MySQL Aggregation Functions Cheatsheet

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;

📚Cheatsheets