MySQL GROUP BY Clause

The GROUP BY clause in MySQL is used to group rows that have the same values in specified columns into summary rows. It is often used with aggregate functions (COUNT(), SUM(), AVG(),MAX(), MIN()) to perform calculations on each group.

Why Use GROUP BY?

• To summarize data
• To perform calculations on grouped data
• To create reports and statistics
• To analyze data patterns

Basic Syntax

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

Common Aggregate Functions

Examples

1. Simple GROUP BY Example

-- Count employees in each department
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department;

2. GROUP BY with Multiple Columns

-- Group by department and job title
SELECT department, job_title, COUNT(*) as count
FROM employees
GROUP BY department, job_title;

3. GROUP BY with WHERE Clause

-- Count employees in each department with salary > 50000
SELECT department, COUNT(*) as employee_count
FROM employees
WHERE salary > 50000
GROUP BY department;

4. GROUP BY with ORDER BY

-- Departments with highest average salary first
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

5. GROUP BY with HAVING Clause

-- Departments with more than 3 employees
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 3;

Sample Data Table

Important Notes

• All non-aggregated columns in SELECT must be in GROUP BY
• GROUP BY comes after WHERE but before ORDER BY
• Use HAVING for filtering grouped results
• NULL values are grouped together