GROUP BY multiple columns in SQL

Introduction

We use GROUP BY multiple columns to organize data into groups based on more than one category. This helps us see patterns or summaries for combinations of values.

When you want to count sales by both store and product.

When you want to find the average score by student and subject.

When you want to summarize expenses by month and category.

When you want to group employees by department and job title.

When you want to analyze website visits by country and device type.

Syntax

SQL

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

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

Columns in SELECT that are not aggregated must be in the GROUP BY clause.

Examples

This groups sales by city and product, then counts how many sales each group has.

SQL

SELECT city, product, COUNT(*)
FROM sales
GROUP BY city, product;

This finds the average salary for each combination of department and job title.

SQL

SELECT department, job_title, AVG(salary)
FROM employees
GROUP BY department, job_title;

This sums expenses grouped by year and month.

SQL

SELECT year, month, SUM(expense)
FROM expenses
GROUP BY year, month;

Sample Program

This example creates a sales table, inserts some data, then groups the sales by store and product to find total quantity sold for each group.

SQL

CREATE TABLE sales (
  store VARCHAR(20),
  product VARCHAR(20),
  quantity INT
);

INSERT INTO sales VALUES
('StoreA', 'Apples', 10),
('StoreA', 'Oranges', 5),
('StoreB', 'Apples', 7),
('StoreB', 'Oranges', 3),
('StoreA', 'Apples', 2);

SELECT store, product, SUM(quantity) AS total_quantity
FROM sales
GROUP BY store, product
ORDER BY store, product;

Important Notes

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

You can group by as many columns as you need to get detailed summaries.

Ordering results after grouping helps read the output clearly.

Summary

GROUP BY multiple columns groups data by combinations of those columns.

Use it to get summaries like counts or sums for each group.

Remember to include all grouped columns in SELECT and GROUP BY.