MySQL — Aggregate Functions

COUNT

-- Count all rows
SELECT COUNT(*) FROM users;

-- Count non-null values
SELECT COUNT(email) FROM users;

-- Count unique values
SELECT COUNT(DISTINCT category) FROM products;

SUM

-- Total price
SELECT SUM(price) FROM products;

-- Total by category
SELECT category, SUM(price) as total
FROM products
GROUP BY category;

AVG

-- Average price
SELECT AVG(price) FROM products;

-- Average by category
SELECT category, AVG(price) as avg_price
FROM products
GROUP BY category;

MIN and MAX

-- Minimum price
SELECT MIN(price) FROM products;

-- Maximum price
SELECT MAX(price) FROM products;

-- Min and max by category
SELECT
    category,
    MIN(price) as min_price,
    MAX(price) as max_price
FROM products
GROUP BY category;

Aggregate Functions Examples

-- Sales summary
SELECT
    COUNT(*) as total_orders,
    SUM(total) as total_revenue,
    AVG(total) as avg_order_value,
    MIN(total) as min_order,
    MAX(total) as max_order
FROM orders;

-- User statistics
SELECT
    COUNT(*) as total_users,
    AVG(age) as avg_age,
    MIN(age) as youngest,
    MAX(age) as oldest
FROM users;

Mini Practice

Write MySQL code that:

1. Counts rows
2. Sums values
3. Calculates average
4. Finds min and max

Up Next

Next: Learn about Joins.