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.