MySQL COUNT, AVG, SUM

Key Aggregate Functions

SELECT COUNT(*)
FROM customers;

SELECT AVG(salary)
FROM employees;

SELECT SUM(amount)
FROM orders;

SELECT category,
       COUNT(*)
FROM products
GROUP BY category;

🔹 COUNT Function

COUNT() returns the number of rows that match your criteria. Use COUNT(*) to count all rows including NULLs, or COUNT(column_name) to count only non-NULL values in a specific column. It's perfect for getting totals and statistics.

-- Count all customers
SELECT COUNT(*) AS total_customers
FROM customers;

-- Count customers with email
SELECT COUNT(email) AS customers_with_email
FROM customers;

-- Count distinct cities
SELECT COUNT(DISTINCT city) AS unique_cities
FROM customers;

🔹 AVG Function

AVG() calculates the average (mean) of numeric values in a column. It automatically ignores NULL values in the calculation. This function is essential for finding typical values like average salary, average price, or average rating in your dataset.

-- Calculate average salary
SELECT AVG(salary) AS average_salary
FROM employees;

-- Calculate average product price
SELECT AVG(price) AS avg_price
FROM products;

-- Average with rounding
SELECT ROUND(AVG(rating), 2) AS avg_rating
FROM reviews;

🔹 SUM Function

SUM() adds up all numeric values in a column, giving you the total. NULL values are ignored. Use SUM for calculating totals like total sales, total inventory value, or total hours worked. It only works with numeric data types.

-- Calculate total sales
SELECT SUM(amount) AS total_sales
FROM orders;

-- Calculate total inventory value
SELECT SUM(price * quantity) AS inventory_value
FROM products;

-- Sum of salaries by department
SELECT 
    department,
    SUM(salary) AS total_payroll
FROM employees
GROUP BY department;

🔹 Combining Multiple Aggregates

You can use multiple aggregate functions in a single query to get comprehensive statistics. This provides a complete overview of your data in one result set, showing counts, averages, totals, and ranges all together for better analysis and reporting.

-- Complete product statistics
SELECT 
    COUNT(*) AS total_products,
    AVG(price) AS avg_price,
    SUM(price) AS total_value,
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive
FROM products;

-- Employee statistics
SELECT 
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    SUM(salary) AS total_payroll,
    MIN(salary) AS lowest_salary,
    MAX(salary) AS highest_salary
FROM employees;

🔹 Using GROUP BY

GROUP BY divides rows into groups and applies aggregate functions to each group separately. This is powerful for category-based analysis, allowing you to compare statistics across departments, regions, product categories, or any other grouping you need for business insights.

-- Count products by category
SELECT 
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
GROUP BY category;

-- Sales by month
SELECT 
    MONTH(order_date) AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS monthly_sales
FROM orders
GROUP BY MONTH(order_date);

-- Employees by department
SELECT 
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

🔹 HAVING Clause

HAVING filters groups after aggregation, while WHERE filters rows before aggregation. Use HAVING when you need to filter based on aggregate function results, such as finding categories with more than 10 products or departments with average salary above a threshold.

-- Categories with more than 10 products
SELECT 
    category,
    COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

-- Departments with avg salary > 60000
SELECT 
    department,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

-- Customers with total orders > 1000
SELECT 
    customer_id,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

🔹 Practical Examples

Real-world business scenarios using aggregate functions for reporting and analysis:

-- Monthly sales report
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS total_orders,
    SUM(amount) AS revenue,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month DESC;

-- Product performance by category
SELECT 
    category,
    COUNT(*) AS products,
    AVG(rating) AS avg_rating,
    SUM(sales) AS total_sales
FROM products
GROUP BY category
HAVING AVG(rating) >= 4.0
ORDER BY total_sales DESC;

-- Customer segmentation
SELECT 
    CASE 
        WHEN total_spent >= 1000 THEN 'Premium'
        WHEN total_spent >= 500 THEN 'Regular'
        ELSE 'New'
    END AS customer_type,
    COUNT(*) AS customer_count
FROM (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
) AS customer_totals
GROUP BY customer_type;