How do you group data with GROUP BY in SQL?
GROUP BY clause in SQL
It is used to collect the identical data into groups. It often goes together with aggregate functions like SUM, COUNT, AVG, MIN, and MAX for performing different operations on every group.
Basic Syntax
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;
column1 is the column to group by
aggregate_function(column2) is the operation done on the grouped data.
Example 1: Grouping with COUNT
Suppose you have an employees table with columns defined for department and salary, and you want to list how many employees are available in each department.
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
This will provide each department along with its employee count.
You can use sum to determine the total salary disbursed by department:
SELECT department, SUM(salary)
FROM employees
GROUP BY department;
This returns each department with the total salary for all employees within that department.
Example 3: Multiple Columns in GROUP BY
You can also group on multiple columns. Here you would group on department and job_title :
SELECT department, job_title, COUNT(*)
FROM employees
GROUP BY department, job_title;
This groups the employees by department and then job title and counts how many employees there are for any given combination.
Filtering Grouped Data with HAVING
The HAVING clause can permit you to filter the groups after an aggregation is complete. Thus, to show how many departments have over 10 employees:
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;
That query returns only the departments with over 10 employees.
Important Terms
GROUP BY rearranges equal data into groups; generally it is applied together with aggregate functions.
HAVING is used to filter groups based on aggregate conditions; WHERE filters rows before grouping.
