GROUP BY in MySQL

The GROUP BY clause in MySQL is used to group rows that have the same values in a specified column and apply aggregate functions (such as COUNT(), SUM(), AVG(), etc.) to each group.

1. Syntax

SELECT column_name, aggregate_function(column_name)FROM table_nameGROUP BY column_name;

• column_name: The column used for grouping.
• aggregate_function: A function like COUNT(), SUM(), AVG(), etc.

2. Example: GROUP BY with COUNT()

employees Table

Query: Count employees per department

SELECT department, COUNT(*) AS total_employeesFROM employeesGROUP BY department;

✅ Output:

+------------+----------------+| department | total_employees |+------------+----------------+| IT         | 2              || HR         | 2              || Sales      | 1              |+------------+----------------+

🚀 Explanation:

• Groups employees by department.
• Uses COUNT(*) to count the number of employees in each department.

3. GROUP BY with SUM()

Query: Total salary per department

SELECT department, SUM(salary) AS total_salaryFROM employeesGROUP BY department;

✅ Output:

+------------+--------------+| department | total_salary |+------------+--------------+| IT         | 115000       || HR         | 102000       || Sales      | 48000        |+------------+--------------+

4. GROUP BY with Multiple Columns

You can group by more than one column.

Query: Count employees by department and salary

SELECT department, salary, COUNT(*) AS totalFROM employeesGROUP BY department, salary;

✅ Groups by both department and salary.

5. GROUP BY with HAVING

• HAVING is used to filter grouped results.
• WHERE cannot be used with aggregate functions.

Query: Show departments with more than 1 employee

SELECT department, COUNT(*) AS total_employeesFROM employeesGROUP BY departmentHAVING total_employees > 1;

✅ Filters only departments with more than 1 employee.

6. GROUP BY vs. ORDER BY

Example: GROUP BY with ORDER BY

SELECT department, COUNT(*) AS total_employeesFROM employeesGROUP BY departmentORDER BY total_employees DESC;

✅ Sorts departments by employee count in descending order.

Key Takeaways

✅ GROUP BY groups duplicate values.
✅ Works with aggregate functions (COUNT(), SUM(), AVG(), etc.).
✅ Use HAVING to filter grouped results.
✅ Combine with ORDER BY for sorted output.