📚Cheatsheets

Cheatsheet collection for go, rust, python, shell and javascript.

MySQL Aggregation Functions Cheatsheet

This cheatsheet covers the common aggregation functions in MySQL, their usage, and examples.

  1. Basic Aggregation Functions
    • COUNT()
    • SUM()
    • AVG()
    • MAX()
    • MIN()
  2. Advanced Aggregation Functions
    • GROUP_CONCAT()
    • DISTINCT with Aggregation
  3. Using Aggregation Functions with GROUP BY
  4. HAVING Clause
  5. Window Functions
  6. 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;