Group by Multiple Columns MySQL

Introduction

The technique of aggregating and summarising data based on one or more columns is known as grouping data in MySQL. When working with enormous datasets and attempting to gain insight into the data, this might be helpful. MySQL's GROUP BY clause enables programmers to organize data based on one or more columns. Developers can execute aggregate operations on the data by grouping them, such as computing the count, average, or sum of a given column.

Basic GROUP BY Clause

Let's first grasp the fundamental syntax of the GROUP BY clause in MySQL before moving on to grouping data using multiple columns. The GROUP BY clause's basic syntax is as follows:

The GROUP BY clause takes the column name as the parameter on which basis the rows will be grouped, as shown above. Also, generally, a specific attribute is aggregated to provide a single value that represents that group.

Example 1: Keeping track of the number of students by subject

students Table:

Query: We can use the following query to count the number of students in each subject:

Output:

Example 2: Counting the number of orders received from each customer

orders Table:

Query: We can use the following query to count the number of orders for each customer:

Output:

GROUP BY Multiple Columns

Now that we are familiar with the GROUP BY clause's fundamental structure, let's see how to group data using several columns. Data grouped using multiple columns can be grouped similarly to data grouped using a single column. The fact that we specify numerous columns to group the data by is the sole difference.

To group many records with the same (or similar) values for the designated columns, use the GROUP BY multiple columns function. Anytime we do a GROUP BY multiple columns, all the rows which have all the column values mentioned with GROUP BY as the same value will be grouped.

By using more than one column as a criterion for grouping, the GROUP BY multiple-columns technique obtains grouped column values from one or more database tables.

The following syntax is used to group data using multiple columns:

With the technique shown above, we may group the data by several columns that are separated by commas after the GROUP BY clause.

Example 1: Calculating the number of students by class and subject

students Table:

Query:

Output:

Example 2: Finding the total profit by item and year

sales Table:

Query: We can use the following query to determine the overall profits for each product by year:

Output:

Using Aggregate Functions with GROUP BY Multiple Columns

We can execute aggregate functions on data grouped by many columns just like when using a single column to group the data. The aggregate function is specified after the column we wish to use for the grouping.

It's also common to compute values for each group when combining data based on numerous columns using aggregate functions like SUM, COUNT, AVG, MAX, and MIN. Data can be summarised using these features, which can also reveal patterns and trends in the data.

Simply include the function in your SELECT statement together with the columns you want to group by, if you want to use an aggregate function, with GROUP BY multiple columns. Each set of rows with the same combination of values in the grouping columns will have the function applied to them.

Example:

orders Table:

Consider, for instance, the above-given table named orders containing details on customer orders, such as the customer's name, the order date, and the total amount. You need to determine the cumulative sales for each client by year and month. Here's how you could accomplish this using the SUM function and the GROUP BY many columns:

Query:

In this example, we'll group by all three columns (customer name, year, and month) after extracting the year and month from the order date column using the YEAR() and MONTH() functions. The SUM function is also being used to determine the total order amount for each group.

Output:

Based on the information in the orders table, this output displays the total sales for each customer by year and month. The total amount column has been subjected to the SUM function, and the customer name, year, and month columns have been used to aggregate the outcomes.

By summarising your data in different ways, aggregate functions using GROUP BY several columns can give you insightful information about your data.

GROUP BY with JOINs

The data can be aggregated from other tables and the results can be grouped based on one or more columns using GROUP BY with JOIN function in MySQL. This is helpful when aggregating and extracting data from related tables.

When the JOIN and a GROUP BY clause are used together, MySQL will combine the rows from the specified tables based on a common column and then divide the result set into groups based on one or more columns. Using the aggregated data in this fashion, you may execute aggregate operations and get more insightful conclusions from the data.

The syntax for using GROUP BY with JOINs is as follows:

Using GROUP BY with JOIN is illustrated by the following example:

customers Table:

orders Table:

By using a join between the orders and the customers tables on the customer_id column and then grouping the result set by the customer_name column, we can obtain the total profit of all orders placed by each customer as follows:

Query:

Output:

We can see from the result that we grouped the data from the customers table by customer_name and computed the total profit amount for every customer from the orders table.

See Also

1. MySQL Joins
2. GROUP BY with subqueries in MySQL
3. DISTINCT in MySQL