Group By

The GROUP BY clause is a powerful feature of the Structured Query Language (SQL) that allows you to group the result set of a query by one or more columns. This clause is commonly used in combination with aggregate functions, such as COUNT, SUM, AVG, MIN, and MAX, to calculate summary statistics for each group.

Syntax

The syntax of the GROUP BY clause is as follows:

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY column1, column2, ...;

In this syntax, column1, column2, etc. are the columns you want to group by, and aggregate_function(column) is the aggregate function that you want to apply to each group. The aggregate function can be any of the aforementioned functions or any other valid SQL function.

Example

For example, suppose you have a table named employees with the following columns: id, name, department, and salary. If you want to calculate the average salary for each department, you can use the following SQL query:

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

This query groups the employees by department and calculates the average salary for each group.

You can also group by multiple columns. For example, if you want to calculate the average salary for each department and job title, you can use the following query:

SELECT department, job_title, AVG(salary)
FROM employees
GROUP BY department, job_title;

This query groups the employees by department and job title and calculates the average salary for each group.

The GROUP BY clause can be very useful when working with large datasets and when you need to perform calculations on subsets of the data. It allows you to group the data into manageable subsets and perform calculations on each subset.