MySQL GROUP BY Query for Grouping

What is the GROUP BY Clause?

The GROUP BY clause in MySQL is used to group rows that have the same values in one or more columns. After grouping, you can apply aggregate functions such as COUNT, SUM, AVG, MAX, and MIN to summarize the data within each group.

Basic Syntax:

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

• column1, column2 – Columns to group by.
• aggregate_function(column3) – Function applied to each group (e.g., SUM, AVG).
• table_name – The table containing the data.

Setting Up the Example Table

Consider the following employees table:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

This table will serve as the basis for demonstrating GROUP BY.

Using GROUP BY with COUNT()

GROUP BY is often used with COUNT() to determine how many records exist in each category.

Example 1: Count Employees by Department

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

Step-by-Step Analysis:

• COUNT(*) counts all employees in each department.
• GROUP BY department groups the rows by department name.
• The query returns one row per department with the number of employees.

Logic Behind the Query:
MySQL first groups all rows sharing the same department. Then it counts the number of rows in each group, producing a summarized result.

Using GROUP BY with SUM()

SUM() allows you to calculate the total of numeric values within each group.

Example 2: Total Salary per Department

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

Step-by-Step Analysis:

• SUM(salary) adds all salaries within each department.
• GROUP BY department ensures calculations are per department.
• This query is useful for payroll budgeting and financial analysis.

Using GROUP BY with Multiple Aggregate Functions

You can combine multiple aggregate functions to get a complete summary.

Example 3: Department Summary

SELECT department,
       COUNT(*) AS employee_count,
       AVG(salary) AS avg_salary,
       MAX(salary) AS highest_salary,
       MIN(salary) AS lowest_salary
FROM employees
GROUP BY department;

Logic Behind the Query:

• Each aggregate function is calculated for every department group.
• Returns comprehensive statistics: number of employees, average salary, highest salary, and lowest salary per department.
• This provides a powerful way to analyze team performance and compensation.

Grouping by Multiple Columns

You can group by more than one column to create hierarchical summaries.

Example 4: Count Employees by Department and Hire Year

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

Step-by-Step Analysis:

• YEAR(hire_date) extracts the year from the hire date.
• GROUP BY department, hire_year creates groups for each combination of department and hire year.
• COUNT(*) calculates the number of employees in each group.

Logic Behind the Query:
MySQL first groups rows by department and then further subdivides them by hire year. Aggregate functions are then applied within each subgroup.

Using GROUP BY with ORDER BY

Combining GROUP BY with ORDER BY makes the results easier to interpret.

Example 5: Departments Ordered by Average Salary

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

Step-by-Step Analysis:

• Groups employees by department.
• Calculates the average salary per department.
• Sorts the results from the highest to lowest average salary.

Principle Behind the Logic:
MySQL applies GROUP BY first to create groups, then calculates the aggregate values, and finally sorts the summarized results based on the aggregate.

Best Practices for Using GROUP BY

1. Always Include Aggregate Functions for Non-Grouped Columns: Only columns used in GROUP BY or aggregated should appear in the SELECT clause.
2. Combine with ORDER BY for Readable Reports: Sorting grouped results improves clarity.
3. Use Meaningful Aliases: Assign descriptive names to calculated columns with AS.
4. Optimize Performance: Index columns used in GROUP BY to speed up queries.
5. Test with Small Datasets: Validate grouping logic before applying it to large tables.

Common Use Cases for GROUP BY

• Counting users by subscription type.
• Summing sales revenue per region.
• Calculating average salary per department.
• Identifying maximum and minimum transaction amounts by category.
• Creating reports for dashboards and management analysis.