MySQL GROUP BY clause

➔ MySQL's GROUP BY clause creates a summary row by grouping rows that have similar values ​​in a specific column.

➔ The GROUP BY clause is almost always used with aggregate functions such as SUM() (total), COUNT() (total records), AVG() (average), MIN() (minimum), and MAX() (maximum).

➔ WHERE filters the rows before GROUP BY (grouping), while HAVING filters the results after GROUP BY (grouping).

➔ Data can be grouped by multiple columns to create more granular and specific categories.

➔ Any column in the SELECT list that is not part of an aggregate function must be included in the GROUP BY clause to avoid errors.

➔ GROUP BY usually sorts the result set according to the grouped columns, so ORDER BY is optional for grouped columns.

MySQL GROUP BY syntax

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

Grouping by a Single Column

Example: Find out how many employees work in each department (group by department_id column)

SELECT department_id, COUNT(*) AS total_employees
FROM employees
GROUP BY department_id;

Example: Find the average salary for each department (group by department ID column).

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

Grouping by Multiple Columns

Example: Determine the number of employees and total salary for each unique combination of department_id and job_id.

SELECT department_id, job_id, COUNT(*) AS employee_count, SUM(salary) AS total_payroll
FROM employees
GROUP BY department_id, job_id;

GROUP BY clause using numeric position

Instead of explicitly specifying the column name, we can use its numeric position. This shorthand method uses a 1-based index, where 1 represents the first column of the query, 2 represents the second, and so on.

Example: Find departments with total number of employees.

SELECT department_id, COUNT(*) 
FROM employees 
GROUP BY 1;

Example: This MySQL query counts the number of employees for each distinct combination of department and job title.

SELECT department_id, job_id, COUNT(*) 
FROM employees 
GROUP BY 1, 2;

Filtering groups with HAVING

Example: Find the departments with total number of employees and average salary where the average salary is more than 3000

SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS average_payroll
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 3000;

Example: Find the departments that have more than 2 employees and list their total number of employees and average salary.

SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS average_payroll
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 2;

Using ORDER BY with GROUP BY clause.

Example: Find the departments with total number of employees and total payroll and sort the result by number of department employees.

SELECT department_id, COUNT(*) AS total_employees, SUM(salary) AS total_payroll
FROM employees
GROUP BY department_id
ORDER BY total_employees DESC;

GROUP BY clause using WITH ROLLUP

➔ In MySQL, the WITH ROLLUP modifier is used with the GROUP BY clause to create super-aggregate rows, such as subtotals and grand totals, within a single result set.

Explanation: The GROUP BY department_id, job_id WITH ROLLUP command creates summary rows for each grouping level. For instance, if you group by department_id and job_id, it will provide totals for each job_id, subtotals for each department_id, and a grand total for all department_id combined. The order is determined from left to right. For example, the GROUP BY A, B WITH ROLLUP command returns the subtotal of A, while the GROUP BY B, A WITH ROLLUP command returns the subtotal of B.

Example: Adding WITH ROLLUP computes the grand total of all departments

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id WITH ROLLUP;

Multi-Level WITH ROLLUP

We can use WITH ROLLUP on multiple columns for more granularity (for example, department_id > job_id).

Example: This query shows salaries by department_id and job_id, plus subtotals for each department_id, and finally the grand total.

SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id, job_id WITH ROLLUP;