MySQL GROUP BY Clause
MySQL GROUP BY Clause
The MySQL GROUP BY
clause is used to arrange identical data into groups. This is essential for aggregating data and applying aggregate functions like COUNT
, SUM
, AVG
, MAX
, and MIN
.
Syntax
SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;
The GROUP BY
clause has the following components:
column1
: The column by which to group the results.aggregate_function(column2)
: An aggregate function applied to the column(s) being grouped.table_name
: The name of the table from which to retrieve the data.condition
: An optional condition to filter the rows before grouping.
Example MySQL GROUP BY Clause
Let's look at some examples of the MySQL GROUP BY
clause:
Step 1: Using the Database
USE mydatabase;
This query sets the context to the database named mydatabase
.

Step 2: Creating a Table
Create a table to work with:
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10, 2)
);
This query creates a table named employees
with columns for id
, first_name
, last_name
, department
, and salary
.

Step 3: Inserting Initial Rows
Insert some initial rows into the table:
INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('John', 'Doe', 'HR', 50000.00),
('Jane', 'Smith', 'IT', 60000.00),
('Jim', 'Brown', 'IT', 55000.00),
('Jake', 'White', 'HR', 52000.00),
('Jill', 'Green', 'Marketing', 45000.00);
This query inserts five rows into the employees
table.

Step 4: Grouping and Counting Rows
Group rows by a column and count them:
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
This query groups rows by the department
column and counts the number of employees in each department.

Step 5: Grouping and Summing Values
Group rows by a column and sum values:
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
This query groups rows by the department
column and sums the salary
for each department.

Step 6: Grouping and Averaging Values
Group rows by a column and average values:
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;
This query groups rows by the department
column and calculates the average salary
for each department.

Step 7: Using GROUP BY with WHERE Clause
Group rows with a condition:
SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE salary > 50000
GROUP BY department;
This query groups rows by the department
column and counts the number of employees in each department where the salary
is greater than 50000.

Step 8: Using GROUP BY with ORDER BY
Group rows and sort the results:
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY average_salary DESC;
This query groups rows by the department
column, calculates the average salary
for each department, and sorts the results by the average salary
in descending order.

Conclusion
The MySQL GROUP BY
clause is a powerful tool for aggregating data and applying aggregate functions. Understanding how to use the GROUP BY
clause is essential for effective data analysis and reporting in MySQL.