How to Use the GROUP BY Statement in MySQL

Contents
In this article, you will learn how to use the GROUP BY statement in MySQL.
Using the GROUP BY statement in MySQL
The GROUP BY statement in MySQL is used to group together rows in a table based on one or more columns.
Syntax
The basic syntax of the GROUP BY statement in MySQL is as follows:
SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY column1, column2, ...;
In this syntax, “column1, column2, …” refers to one or more columns from the table that we want to group by. The aggregate function is used to perform a calculation on the values in a column. The most commonly used aggregate functions in MySQL are COUNT, SUM, AVG, MIN, and MAX.
Examples
Suppose we have a table called “sales” with columns “id”, “product_name”, “category”, and “price”. We want to calculate the total sales for each category.
SELECT category, SUM(price) as total_sales
FROM sales
GROUP BY category;
In this example, we are grouping the rows in the “sales” table by the “category” column and using the SUM function to calculate the total sales for each category.
And suppose we have a table called “orders” with columns “id”, “customer_id”, and “order_date”. We want to calculate the number of orders placed by each customer.
SELECT customer_id, COUNT(*) as num_orders
FROM orders
GROUP BY customer_id;
In this example, we are grouping the rows in the “orders” table by the “customer_id” column and using the COUNT function to calculate the number of orders placed by each customer.
And suppose we have a table called “employees” with columns “id”, “name”, “department”, and “salary”. We want to calculate the average salary for each department.
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;
In this example, we are grouping the rows in the “employees” table by the “department” column and using the AVG function to calculate the average salary for each department.
Using multiple columns
The GROUP BY statement can also be used to group rows based on multiple columns. Here is an example:
SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY column1, column2, ...;
In this syntax, “column1” and “column2” refer to two columns from the table that we want to group by.
Filtering groups
The HAVING clause can be used with the GROUP BY statement to filter groups based on a condition. Here is an example:
SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY column1, column2, ...
HAVING condition;
In this syntax, “condition” is a logical expression that is used to filter groups based on a condition.
Performance considerations
The GROUP BY statement can have a significant impact on the performance of a query, especially if the table being queried has a large number of rows. It is generally recommended to use the GROUP BY statement only when necessary and to use appropriate indexing to optimize performance.