In MySQL, the GROUP BY clause is a powerful tool for grouping rows with the same values into summary rows, enabling efficient data analysis. It is often used with aggregate functions like SUM, COUNT, AVG, MIN, and MAX to perform calculations on grouped data.

In this article, We will learn about the MySQL Group By Clause by the understanding with the various aggregate functions examples and so on.

MySQL Group By Clause

• In MySQL, the GROUP BY clause is useful operator that is used to group rows that have the same values, and can be used to push these values into summary rows, like “find the number of customers in each city” or “calculate the total number of sales per product category.”
• It is often used along with aggregate functions like SUM, COUNT, AVG, MIN, and MAX to perform calculations on grouped data.

Syntax:

The GROUP BY clause is used to group rows that have the same values into summary rows. It operates on a set of columns that are specified in the SQL query. The basic syntax involving the GROUP BY operation is given below:

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;

Where,

• column1: Columns by which you want to group the result set.
• aggregate_function: Functions like SUM, COUNT, AVG, etc., to perform calculations on the grouped data.
• table_name: The table name from which you are fetching the data.
• condition: Conditions to filter rows before grouping. This is optional if you are giong to perform just a raw and simple query

Examples of MySQL Group By Clause

Before we get into exploring the GROUP BY Clause , we shall see the details of the employees table that we are going to use for our understanding.

Example 1: Grouping by a Single Column

Lets say that you have a table named employees with column such as customer_id, order_date, and total_amount. We want to find the average salary obtained by each department( such as IT, HR, Marketing etc).

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Output:

FIG: Query Result showing the average salary of each dept. grouped by a single column

This query will group rows from the orders table by customer_id and calculate the sum of total_amount for each customer. The result will show each customer_id alongside the total amount they have spent.

Thus by executing the above query , we can find out the average salary that each department employees recieve, and amoung them the finance department marks to have the highest average.

Example 2: Grouping by Multiple Columns

Consider a same employees table ,where this time we will find the average_salary of each employee and retrive the result along with the department they work in.

The query finds the average_salary and returns the tables with the department and the name of the employee along with the average salary.

SELECT department, name, AVG(salary) AS average_salary
FROM employees
GROUP BY department , name;

Output:

Fig: Query result showing the grouping by multiple column approch.

This query groups rows from the sales table by both product_id and sale_date. It calculates the sum of quantity for each combination of product_id and sale_date. The result will include each product_id, sale_date pair with the total quantity sold.
Lets see the working of GROUP BY Clause along with the aggregate functions such as SUM, AVG,COUNT,MIN and MAX.

Example 3: MySQL GROUP BY Clause with COUNT Function

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Output:

Fig: Query result showing the employee count in each department

This query above counts the number of employees in each department, which is performed by COUNT aggregation function.

Example 4: MySQL GROUP BY Clause with SUM Function

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

Output:

Fig: Query result showing the tatal salary of each department

This query sums the salaries of employees in each department using the SUM Aggregate function which does the latter job.

Example 5: MySQL GROUP BY Clause with MIN Function

SELECT department, MIN(salary) AS min_salary
FROM employees
GROUP BY department;

Output:

Fig: Query result showing the minimum salary in each department

This query finds the minimum salary in each department, by making use of the MIN aggregate function available in SQL.

Example 6: MySQL GROUP BY Clause with MAX Function

SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department;

Output:

FIg: Query result showing maximum salary of each department

This query finds the maximum salary in each department, which is executed by the use of MAX as the aggregate function.

Example 7: MySQL GROUP BY Clause with AVG Function

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Output:

Fig: Query result showing the average salary of each department

This query calculates the average salary in each department, which is performed by the AVG Function as the aggregation.

Conclusion

Overall, GROUP BY clause in MySQL is an essential tool for data analysis and reporting. It allows you to group rows that have the same values into summary rows and perform calculations on these groups using aggregate functions like SUM, COUNT, AVG, MIN, and MAX. By mastering the GROUP BY clause, you can efficiently analyze and summarize large datasets, making it an invaluable skill for any database professional or data analyst.