Why aggregation summarizes data in MySQL

Introduction

Aggregation helps us combine many rows of data into a single summary. It makes large data easier to understand by showing totals, averages, counts, and more.

When you want to find the total sales made by a store in a month.

When you need to count how many customers visited your website today.

When you want to calculate the average score of students in a class.

When you want to find the highest or lowest price of products in a category.

When you want to group data by categories and see summary information for each.

Syntax

MySQL

SELECT AGGREGATE_FUNCTION(column_name) FROM table_name;

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

You can add GROUP BY to summarize data by groups.

Examples

Counts the total number of rows in the customers table.

MySQL

SELECT COUNT(*) FROM customers;

Calculates the average price of all products.

MySQL

SELECT AVG(price) FROM products;

Shows total quantity sold for each category.

MySQL

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

Sample Program

This example creates a sales table, inserts some data, and then sums the quantity sold for each category.

MySQL

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

INSERT INTO sales VALUES
(1, 'Books', 5),
(2, 'Books', 3),
(3, 'Toys', 7),
(4, 'Toys', 2),
(5, 'Clothes', 4);

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

Important Notes

Aggregation functions ignore NULL values except COUNT(*).

GROUP BY is used to group rows before aggregation.

Summary

Aggregation combines many rows into summary values.

Common functions: SUM, COUNT, AVG, MAX, MIN.

Use GROUP BY to summarize data by categories.