GROUP BY with aggregate functions in SQL

Introduction

GROUP BY helps you organize data into groups so you can calculate totals or averages for each group.

You want to find the total sales for each product.

You need to count how many students are in each class.

You want to calculate the average score for each team.

You want to see the maximum salary in each department.

You want to group customer orders by date to see daily totals.

Syntax

SQL

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

The column in GROUP BY is the one you want to group your data by.

AGGREGATE_FUNCTION can be COUNT, SUM, AVG, MAX, or MIN.

Examples

This counts how many employees are in each department.

SQL

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

This adds up the quantity sold for each product.

SQL

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

This finds the average test score for each class.

SQL

SELECT class, AVG(score)
FROM tests
GROUP BY class;

Sample Program

This example creates a sales table, adds some sales data, and then shows 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

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

GROUP BY helps summarize data by categories.

Summary

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

Aggregate functions calculate values like sum, count, or average for each group.

Use GROUP BY when you want to see summary information for categories.