Grouping data using SQL

When grouping data in SQL, records are grouped based on one or more columns using the GROUP BY clause in a SELECT statement. The GROUP BY clause divides the rows into groups based on the supplied columns and performs aggregate operations on each group, such as SUM, COUNT, AVG, etc.

Here is an illustration of data grouping in SQL. SELECT column1, GROUP BY column1 FROM table_name, SUM(column2); In this illustration, column 1 is used to group the data, while column 2 receives the SUM function for each group. As a result, each group's values for column 1 and the sum of column 2 are displayed in a table.It can also group data by multiple columns by including them in the GROUP BY clause:

SELECT column1, column2, COUNT(*) FROM table_name GROUP BY column1, column2;

This example groups the data by both column1 and column2 and counts the number of rows in each group using the COUNT function.

When using the GROUP BY clause, all columns that are not included in the GROUP BY clause must be used in an aggregate function. This is because the GROUP BY clause creates a summary of the data and any column not included in the GROUP BY clause needs to be aggregated to produce a meaningful result.

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

In this example, the data is grouped by the department column, and the SUM function is used to calculate the total salary for each department.

More Examples

1- Grouping by a single column

SELECT department, COUNT(*) as count

FROM employees

GROUP BY department;

This groups the employees table by the department column and counts the number of employees in each department.

2. Grouping by multiple columns

SELECT department, gender, COUNT(*) as count

FROM employees

GROUP BY department, gender;

This groups the employees table by both the department and gender columns and counts the number of employees in each department and gender combination.

3. Using aggregate functions

SELECT department, AVG(salary) as average_salary

FROM employees

GROUP BY department;

This groups the employees table by the department column and calculates the average salary for each department.

4. Grouping by expressions

SELECT YEAR(date), MONTH(date), SUM(sales) as total_sales

FROM sales

GROUP BY YEAR(date), MONTH(date);

This groups the sales table by the year and month of the date column then calculates the total sales for each month and year combination.