How to Use Group by in MySQL

Introduction

Group by is a more powerful feature in MySQL that allows you to group rows based on a specific column or set of columns. It is commonly used for generating summary reports and performing aggregations on large datasets. This article will discuss how to use (group by) in MySQL with step-by-step examples.

Step 1. Create a table and insert data To demonstrate the use of (group by). We need to create a table and insert some data into it. Here is an example of creating a table called "orders" with some sample data

CREATE TABLE orders (
order_id INT PRIMARY KEY, 
customer_name VARCHAR(50), 
order_date DATE, 
order_total DECIMAL(10, 2) ); 
INSERT INTO 
orders (
order_id, 
customer_name, 
order_date, 
order_total) 
VALUES (1, 'John Doe', '2022-01-01', 100.00), 
(2, 'Jane Smith', '2022-01-02', 50.00), 
(3, 'John Doe', '2022-01-03', 75.00), 
(4, 'Jane Smith', '2022-01-04', 200.00), 
(5, 'John Doe', '2022-01-05', 125.00);

Output 

Step 2. Use Group by clause To group the rows in the "orders" table by customer_name; we can use the (GROUP BY) clause as follows.

​​​​​​​SELECT customer_name, SUM(order_total) AS total_orders FROM orders GROUP BY customer_name;

Output

The above query will group the orders by customer_name and return the total order amount for each customer. Here, we use the SUM() function to calculate the total order amount and alias it as "total_orders".

Step 3. Use (Group by) with multiple columns. We can also group the rows in the "orders" table by multiple columns. For example, if we want to group the orders by customer_name and order_date. We can use the following query-

SELECT customer_name, order_date, SUM(order_total) AS total_orders FROM orders GROUP BY customer_name, order_date;

Output

The above query will group the orders by customer_name and order_date and return the total order amount for each customer for each date.

Step 4. Use (Group by) with aggregate functions. In addition to grouping by columns, we can also perform aggregate functions such as SUM(), AVG(), MAX(), MIN(), and COUNT() on the grouped data. For example, if we want to find the total number of orders placed by each customer, we can use the following query-

SELECT customer_name, order_date, SUM(order_total) AS total_orders FROM orders GROUP BY customer_name, order_date;

The above query will group the orders by customer_name and return the total number of orders placed by each customer.

Step 5. Use HAVING clause The HAVING clause is used to filter the grouped data based on a condition. For example, if we want to find customers who have placed more than two orders, we can use the following query-

SELECT customer_name, COUNT(*) AS total_orders FROM orders GROUP BY customer_name HAVING COUNT(*) > 2;

Output

​​​​​​​

The above query will group the orders by customer_name and return the total number of orders placed by each customer who has placed more than two orders.

Conclusion

In conclusion, the (group by clause is a powerful feature in MySQL that allows you to group rows based on a specific column or set of columns. By using (group by), you can generate summary reports and perform aggregations on large datasets with the help of the examples provided above.