How to Use the GROUP BY Statement in MySQL

08/04/2021

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.

#MySQL