GROUP BY clause in MySQL

Introduction

The GROUP BY clause helps you organize data into groups so you can summarize or count things easily.

When you want to count how many items belong to each category.

When you want to find the total sales for each product.

When you want to see the average score for each student.

When you want to group data by date to see daily totals.

Syntax

MySQL

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

Use GROUP BY with columns that you want to group your results by.

Aggregate functions like COUNT(), SUM(), AVG() work well with GROUP BY.

Examples

Counts how many employees are in each department.

MySQL

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

Adds up the total quantity sold for each product.

MySQL

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

Finds the average temperature for each city.

MySQL

SELECT city, AVG(temperature)
FROM weather
GROUP BY city;

Sample Program

This example creates a sales table, adds some sales data, and then shows the total quantity sold for each product.

MySQL

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

INSERT INTO sales (id, product, quantity) VALUES
(1, 'Apple', 10),
(2, 'Banana', 5),
(3, 'Apple', 7),
(4, 'Banana', 3),
(5, 'Cherry', 8);

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

Important Notes

Every column in SELECT that is not inside an aggregate function must be listed in GROUP BY.

GROUP BY helps you see patterns by grouping similar rows together.

You can use ORDER BY after GROUP BY to sort the grouped results.

Summary

GROUP BY groups rows that have the same values in specified columns.

It is used with aggregate functions to summarize data.

It helps answer questions like 'how many', 'how much', or 'what average' per group.