MySQL GROUP BY: Usage & Syntax Explained

1 year ago
Liam
2 minutes

The GROUP BY statement in MySQL is used to group the query results based on a specified column. It is commonly used in conjunction with aggregate functions (such as SUM, COUNT, AVG, etc.) to perform calculations or summaries on the data within each group.

The basic syntax of the GROUP BY statement is as follows:

SELECT column1, column2, ..., aggregate_function(column_name)
FROM table
WHERE conditions
GROUP BY column1, column2, ...

In this case, column1, column2, etc. are the names of the columns to be queried, aggregate_function is the aggregation function, table is the name of the table, and conditions are the query conditions.

The execution process of the GROUP BY statement is as follows:

  1. The query results are divided into multiple groups based on the column specified in the GROUP BY clause.
  2. Perform calculations or summary operations on each group, using aggregate functions to compute specified columns, such as sum, count, average, etc.
  3. Return the calculation results for each group as the query result.

The scenarios where the GROUP BY statement is used include:

  1. Calculate the quantity and averages of each category.
  2. Search for sales revenue, profits, etc. for each region.
  3. Group statistics by date, year, and month.

It is important to note that the GROUP BY statement must be used with aggregate functions, otherwise an error will occur. Additionally, if the columns in the SELECT clause are not in the GROUP BY clause, they must be parameters of aggregate functions.

For instance, let’s say there is a table called orders that contains information about orders. To calculate the number of orders for each customer, you can use the following GROUP BY statement:

SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id;

This will return the ID of each customer along with the corresponding number of orders.

bannerAds