SQL – Group By

The SQL GROUP BY clause is used in a SELECT statement to group rows that have the same values in one or more columns. The basic syntax for the GROUP BY clause is as follows:

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

The aggregate functions, such as SUM, COUNT, AVG, MIN, and MAX, are used to calculate a single value for each group of rows.

For example, the following SQL query groups the employees by department and returns the number of employees in each department:

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

In this example, the query groups the employees by department_id and uses the COUNT(*) function to return the number of employees in each department.

Another example, the following SQL query groups the employees by department and returns the average salary of employees in each department:

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

In this example, the query groups the employees by department_id and uses the AVG function to return the average salary of employees in each department.

It’s important to note that the GROUP BY clause should be used after the WHERE clause and before the ORDER BY clause in a SELECT statement. Also, the columns that are not included in the GROUP BY clause should be used with an aggregate function.

GROUP BY vs ORDER BY

GROUP BY and ORDER BY are both used to sort and organize data in a SELECT statement, but they have different purposes and are used in different ways.

Here is a table that summarizes the main differences between GROUP BY and ORDER BY:

GROUP BY	ORDER BY
Used to group rows with similar values in one or more columns.	Used to sort the results in ascending or descending order based on one or more columns.
Used with aggregate functions (e.g. COUNT, SUM, AVG) to calculate a single value for each group of rows.	Not used with aggregate functions.
Should be used after the WHERE clause and before the ORDER BY clause in a SELECT statement.	Should be used last in a SELECT statement, after all other clauses.
Can only be used on columns that are specified in the GROUP BY clause.	Can be used on any column in the SELECT statement.

It’s important to note that the GROUP BY clause must be used in conjunction with aggregate functions such as SUM, COUNT, AVG, MIN, and MAX, which are used to calculate a single value for each group of rows. On the other hand, the ORDER BY clause is used to sort the result set, and it’s not necessary to use any aggregate function with it.