GROUP BY with multiple columns in MySQL

Introduction

Grouping data by multiple columns helps you organize and summarize information based on more than one category at the same time.

You want to count how many sales happened for each product in each store.

You need to find the total hours worked by each employee in each department.

You want to see the average score of students grouped by both class and subject.

You want to summarize expenses by both month and category.

Syntax

MySQL

SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2;

You list all the columns you want to group by after GROUP BY, separated by commas.

Aggregate functions like COUNT(), SUM(), AVG() work on other columns to summarize data.

Examples

Counts how many employees are in each role within each department.

MySQL

SELECT department, role, COUNT(*)
FROM employees
GROUP BY department, role;

Adds up total sales for each product in each city.

MySQL

SELECT city, product, SUM(sales)
FROM sales_data
GROUP BY city, product;

Finds average temperature for each month of each year.

MySQL

SELECT year, month, AVG(temperature)
FROM weather
GROUP BY year, month;

Sample Program

This query groups orders by customer and product category, counting orders and summing amounts spent.

MySQL

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  product_category VARCHAR(50),
  order_amount DECIMAL(10,2)
);

INSERT INTO orders VALUES
(1, 101, 'Books', 15.99),
(2, 102, 'Books', 25.50),
(3, 101, 'Electronics', 199.99),
(4, 103, 'Books', 8.99),
(5, 102, 'Electronics', 99.99),
(6, 101, 'Books', 12.00);

SELECT customer_id, product_category, COUNT(*) AS order_count, SUM(order_amount) AS total_spent
FROM orders
GROUP BY customer_id, product_category
ORDER BY customer_id, product_category;

Important Notes

When using GROUP BY with multiple columns, the result groups rows that have the same values in all those columns.

Always include all non-aggregated columns in the GROUP BY clause to avoid errors.

Ordering results after grouping helps read the summary data more easily.

Summary

GROUP BY with multiple columns groups data by all listed columns together.

Use aggregate functions to summarize data within each group.

This helps analyze data across multiple categories at once.