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.

MySQL USE DATABASE

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.

MySQL CREATE TABLE

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.

MySQL INSERT INTO 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.

MySQL GROUP BY COUNT

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.

MySQL GROUP BY SUM

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.

MySQL GROUP BY AVG

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.

MySQL GROUP BY WITH WHERE

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.

MySQL GROUP BY WITH ORDER BY

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.