Why grouping is needed in SQL

Introduction

Grouping helps us organize data into smaller sets based on shared values. This makes it easier to summarize and understand large amounts of information.

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

When you want to find the average score of students in each class.

When you want to sum up total expenses for each month.

When you want to find the highest salary in each department.

When you want to see how many customers live in each city.

Syntax

SQL

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name;

Use GROUP BY to group rows that have the same value in specified columns.

Aggregate functions like COUNT(), SUM(), AVG(), MAX(), MIN() are used to summarize grouped data.

Examples

This counts how many employees are in each department.

SQL

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

This finds the average salary of employees in each city.

SQL

SELECT city, AVG(salary)
FROM employees
GROUP BY city;

This sums the total quantity sold for each product.

SQL

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

Sample Program

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

SQL

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

INSERT INTO sales VALUES
('Apple', 10),
('Banana', 5),
('Apple', 15),
('Banana', 7),
('Orange', 8);

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

Important Notes

Without grouping, aggregate functions would calculate over the entire table, not by categories.

Every column in SELECT that is not aggregated must be in GROUP BY.

Summary

Grouping organizes data into categories based on column values.

It is used with aggregate functions to summarize data per group.

Grouping helps answer questions like totals, averages, counts per category.