How GROUP BY changes query execution in SQL

Introduction

GROUP BY helps organize data into groups so you can summarize or count items easily.

When you want to count how many sales each product has.

When you want to find the average score for each student in a class.

When you want to sum up total expenses by category.

When you want to see how many employees work in each department.

Syntax

SQL

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

You must list the column(s) you want to group by after GROUP BY.

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

Examples

This counts how many employees are in each department.

SQL

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

This sums the quantity sold for each product.

SQL

SELECT product, SUM(quantity)
FROM sales
GROUP BY product;

This finds the average age of customers in each city.

SQL

SELECT city, AVG(age)
FROM customers
GROUP BY city;

Sample Program

This example creates a sales table, adds some sales data, and then groups the data by product to find total quantity sold per product.

SQL

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

INSERT INTO sales VALUES
('Apple', 10),
('Banana', 5),
('Apple', 7),
('Banana', 3),
('Cherry', 8);

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

Important Notes

GROUP BY changes the query to work on groups of rows, not individual rows.

Aggregate functions like SUM, COUNT, AVG work with GROUP BY to summarize data.

Without GROUP BY, aggregate functions consider the whole table as one group.

Summary

GROUP BY groups rows with the same values in specified columns.

It allows aggregate functions to calculate summaries per group.

Use GROUP BY when you want to analyze data by categories or groups.