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 columnThis 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 departmentThis 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 departmentThis 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 departmentThis 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 departmentThis 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 departmentThis 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.
Similar Reads
SQLite Group By Clause SQLite is a server-less database engine and it is written in C programming language. It is developed by D. Richard Hipp in the year 2000. The main moto for developing SQLite is to escape from using complex database engines like MYSQL etc. It has become one of the most popular database engines as we
6 min read
PL/SQL GROUP BY Clause The GROUP BY clause in PL/SQL is a powerful tool used to organize data into aggregated groups based on one or more columns. It is essential for performing summary operations on large datasets, enabling efficient data analysis by grouping rows that share common values.In this article, We will learn a
7 min read
PostgreSQL - GROUP BY clause The GROUP BY clause in PostgreSQL is an essential tool that allows us to group rows that share the same values in one or more columns. This powerful functionality is commonly used to perform aggregate calculations such as SUM(), COUNT(), AVG(), and more, enabling us to summarize data efficiently. In
4 min read
MariaDB GROUP BY Clause An important feature in MariaDB, and SQL in general, is the GROUP BY clause. This article explains the important features of the GROUP BY clause in MariaDB. This article explains you the syntax of the GROUP BY clause and its practical applications. Examples include using the aggregate functions, gro
6 min read
SQL COUNT() with GROUP BY Clause The SQL COUNT() function is a powerful tool used to count the number of rows in a dataset. When combined with the GROUP BY clause, it helps group data by specific attributes and count rows within each group. This is particularly useful for summarising data and generating insights.In this article, we
3 min read