MySQL GROUP BY Statement

The MySQL GROUP BY statement is used to combine rows that have the same value in one or more columns. It is mainly used when we want to create a summary from a table. The GROUP BY clause is used with aggregate functions such as COUNT(), SUM(), AVG(), MAX(), and MIN().

For example, suppose an employees table has employees from different departments. We can use GROUP BY to put employees from the same department into one group and then calculate the total salary, average salary, or number of employees in each department.

What Is GROUP BY in MySQL?

The GROUP BY clause is used to group rows with the same values in one or more columns. It is commonly used with aggregate functions such as COUNT(), SUM(), and AVG() to calculate summary information from a table.

For example, if a table contains employees from the IT, HR, and Sales departments, GROUP BY department creates three groups:

• IT employees
• HR employees
• Sales employees

We can perform calculations on each group.

Syntax of GROUP BY

The basic syntax of the GROUP BY clause is:

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

Here:

• column1: The column used to create groups.
• aggregate_function(): A function such as COUNT(), SUM(), AVG(), MAX(), or MIN().
• column2: The column on which the calculation is performed.
• table_name: The name of the table from which data is retrieved.
• WHERE condition: An optional condition used to filter rows before grouping.

Example

Suppose we have an employees table and we want to find the total salary by each department.

Query

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

Output

Explanation

Here, GROUP BY department puts employees with the same department into one group. The SUM(salary) function then adds the salaries within each group. So, the result shows the total salary for each department.

GROUP BY with COUNT()

The COUNT() function is used to count rows in each group. For example, we can find the number of employees working in each department.

Query

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

Output

GROUP BY with SUM()

The SUM() function is used to add numeric values within each group. For example, we can calculate the total salary for every department.

Query

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

Output

GROUP BY with AVG()

The AVG() function is used with GROUP BY to calculate the average value for each group.

Query

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

Output

GROUP BY with MAX() function

The MAX() function is used with GROUP BY to find the highest value in each group.

Query

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

Output

GROUP BY with MIN()

The MIN() function is used with GROUP BY to find the smallest value in each group.

Query

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

Output

GROUP BY with WHERE

The WHERE clause can be used before GROUP BY to filter individual rows. For example, suppose we only want to include employees whose salary is greater than 40000.

Query

SELECT department, COUNT(*) AS employee_count FROM employees WHERE salary > 40000 GROUP BY department;

GROUP BY with HAVING

The HAVING clause is used to filter groups after they have been created. It is especially useful when we want to apply a condition to an aggregate result. For example, suppose we want to display only those departments where the total salary is greater than 50000.

Query

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

GROUP BY with ORDER BY

We can use ORDER BY with GROUP BY when we want to sort the grouped results. For example, the following query sorts departments by total salary from highest to lowest:

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

GROUP BY with JOIN

GROUP BY can also be used with JOIN when data comes from multiple tables. For example, suppose we have orders and customers tables. We can join them and count the number of orders placed by each customer.

SELECT customers.customer_name,       COUNT(orders.order_id) AS total_orders FROM customers JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customers.customer_name;

GROUP BY with DISTINCT Values

GROUP BY can also be used when we simply want one result for each unique value.

For example:

SELECT department FROM employees GROUP BY department;

Output

GROUP BY and Aggregate Functions

GROUP BY is commonly used with aggregate functions because it allows us to perform calculations separately for each group. Some commonly used aggregate functions are: