Grouping Data with GROUP BY
In SQL database management systems, the GROUP BY clause is used to group certain rows of a database table that have similar characteristics in specific table columns. For example, to find the total, average, mean, maximum, or count of numeric values in each group of table data within a database table. The GROUP BY clause is most often used with database numeric functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() to apply these aggregate functions to the rows of a database table.
SQL GROUP BY Clause Syntax.
SELECT column_name(s), aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name(s);
SQL GROUP BY Clause elements.
- column_name(s) – These are the columns in the table that the database user wants to select.
- aggregate_function() – These are the aggregate functions to be applied to the table data, which the user wants to apply to the grouped data. For example, COUNT(), SUM(), AVG(), etc.
- condition – This is an optional filter condition to limit the rows before grouping the table data, used with the WHERE statement.
- GROUP BY – This is the column or columns in the table by which the table data is grouped and displayed.
Example without aggregate functions in SQL database.
If a database user wants to group table data according to one or more columns without applying any aggregate functions, they can use the GROUP BY clause to preview the unique groups of table data values.
Example without aggregate functions.
Let’s assume the database user has a database table named “employee,” and the user wants to find out how many employees are working in each department in the employee table. You can group and preview the table data by department.
SELECT department, COUNT(*)
FROM employe
GROUP BY department;
Table output result.
department COUNT(*)
Marketing 1
Development 2
Design 3
In this example.
- Here, the GROUP BY clause groups and displays the employee departments in the table according to the department.
- Here, the COUNT(*) numeric database function in the GROUP BY clause displays the actual number of employees in each department.
Using Aggregate Functions with the GROUP BY Clause in SQL Databases.
Database users can use the GROUP BY clause with database aggregate functions to apply numeric function calculations to each group of rows in an SQL database table.
Examples with SQL database aggregate functions.
SUM() aggregate function – Here, the SUM function calculates and displays the total salary for each department in the employee table.
SELECT department, SUM(salary)
FROM employe
GROUP BY department;
SUM() aggregate function Result.
department SUM(salary)
Marketing 74000
Development 44000
Design 63000
In this example.
- Here, in this example, the SUM function (salary) column in the employee table calculates and displays the total salary for each department in the employee table.
AVG() aggregate function – This function calculates and displays the average salary in each department in the employee table.
SELECT department, AVG(salary)
FROM employee
GROUP BY department;
AVG() Aggregate function result.
department AVG(salary)
Marketing 74000
Development 44000
Design 63000
In this example.
- Here, the AVG Aggregate function (salary) calculates and displays the average salary for each department in the employee table.
MIN() Aggregate function – Here, the MIN aggregate function finds and displays the minimum salary in each department in the employee table.
SELECT department, MIN(salary)
FROM employee
GROUP BY department;
MIN() Aggregate function result.
department MIN(salary)
Development 44000
Design 63000
Marketing 74000
In this example.
- Here, the MIN aggregate function in the Employee table displays the minimum employee salary in each department in the table row using MIN(salary).
MAX() Aggregate function – Here, the MAX aggregate function finds and displays the maximum employee salary in each department in the Employee table.
SELECT department, MAX(salary)
FROM employe
GROUP BY department;
Result of the MAX() Aggregate function.
department MAX(salary)
Marketing 74000
Design 63000
Development 44000
In this example.
- the MAX aggregate function (salary) in the Employee table finds and displays the maximum salary in each department.
Grouping by Multiple Columns in an SQL Database.
In an SQL database, users can group and display user table data based on multiple columns. This feature is used when the database user wants to analyse table data across several dimensions.
Example of Grouping by Multiple Columns.
Here, the employee table displays the total salary and average salary based on department and age group, such as employees under 20, between 20-40 years old, and over 40 years old.
SELECT department,
CASE
WHEN emp_age < 20 THEN ‘Under 20’
WHEN emp_age BETWEEN 20 AND 40 THEN ’20-40′
ELSE ‘Over 40’
END AS emp_age_group,
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary
FROM employe
GROUP BY department, emp_age_group;
Grouping by Multiple Columns.
For the employee table, the result shows department, age_group, total_salary, and average_salary.
department salary
Marketing 74000
Development 44000
Design 63000
In this example.
- The CASE statement in the employee table creates an emp_age_group category for each employee.
- The table data is grouped and displayed first by department and then by emp_age_group.
- The database table query calculates SUM(salary) and AVG(salary) for each table group.
Filtering Groups with HAVING in an SQL Database Table.
Database users can filter table rows after the GROUP BY clause operation using the HAVING clause. The HAVING clause in a database table works similarly to the WHERE clause, but it is used to filter aggregated results.
Example of filtering groups with HAVING.
To get the departments with a total salary of more than 50,000 from the Employee table.
SELECT department, SUM(salary) AS total_salary
FROM employe
GROUP BY department
HAVING SUM(salary) > 50000;
Result of filtering groups with HAVING.
department total_salary
Design 63000
Marketing 74000
In this example.
- Here, the HAVING clause in the Employee database table filters and displays the results of the SUM(salary) calculation, and the preview displays only those department results where the total salary is greater than 50000.
Sorting groups in an SQL database with ORDER BY.
Database users can use the ORDER BY clause to sort the table result set after adding the GROUP BY clause.
Example of sorting groups with ORDER BY.
To display the departments in descending order of total salary in the Employee table:
SELECT department, SUM(salary) AS total_salary
FROM employe
GROUP BY department
ORDER BY total_salary DESC;
Result of sorting groups with ORDER BY.
department salary
Marketing 74000
Design 63000
Development 44000
In this example.
- Here, the Employee table data is sorted and displayed in descending order of total_salary, with the department having the highest total salary in the current Employee table displayed first.
