JOIN with aggregate functions in MySQL

Introduction

We use JOIN with aggregate functions to combine data from two tables and then calculate totals, averages, or counts on the combined data.

You want to find the total sales for each customer by combining customer and sales tables.

You need to count how many orders each product has by joining products and orders tables.

You want to calculate the average rating for each movie by joining movies and reviews tables.

Syntax

MySQL

SELECT columns, AGGREGATE_FUNCTION(column)
FROM table1
JOIN table2 ON table1.common_column = table2.common_column
GROUP BY columns;

AGGREGATE_FUNCTION can be SUM, COUNT, AVG, MAX, MIN.

GROUP BY is needed to group rows before applying the aggregate function.

Examples

Counts how many orders each customer has.

MySQL

SELECT customers.name, COUNT(orders.id) AS order_count
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name;

Calculates total quantity sold for each product.

MySQL

SELECT products.name, SUM(order_items.quantity) AS total_sold
FROM products
JOIN order_items ON products.id = order_items.product_id
GROUP BY products.name;

Finds average salary per department.

MySQL

SELECT employees.department, AVG(salaries.amount) AS avg_salary
FROM employees
JOIN salaries ON employees.id = salaries.employee_id
GROUP BY employees.department;

Sample Program

This query joins customers with their orders, counts how many orders each customer made, and sums the total amount spent.

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  amount DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO orders VALUES (1, 1, 100.00), (2, 1, 50.00), (3, 2, 75.00);

SELECT customers.name, COUNT(orders.id) AS order_count, SUM(orders.amount) AS total_amount
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name;

Important Notes

Always use GROUP BY when using aggregate functions with JOIN to group results correctly.

JOIN combines rows from two tables based on a related column.

Aggregate functions summarize data, like counting or summing values.

Summary

JOIN with aggregate functions helps combine and summarize data from multiple tables.

Use GROUP BY to group data before applying aggregate functions.

Common aggregate functions include COUNT, SUM, and AVG.