GROUP BY Clause in SQL

GROUP BY Clause in SQL

The GROUP BY clause is a powerful feature in SQL that allows you to group rows in a result set based on one or more columns. It is commonly used with aggregate functions to perform calculations on each group independently. This documentation provides an overview of the GROUP BY clause and demonstrates its usage with proper code examples and explanations.

Syntax:

sql
SELECT column1, aggregate_function(column2)
FROM table
GROUP BY column1

Explanation:

• The SELECT statement specifies the columns to be included in the result set.
• The aggregate_function performs calculations on a specified column or expression within each group.
• The FROM clause specifies the table(s) from which to retrieve the data.
• The GROUP BY clause defines the column(s) used to group the result set.

Example 1:

Consider a table named "Employees" with the following columns: "EmployeeID," "FirstName," "LastName," and "Salary." We want to calculate the total salary for each department.

sql
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;

Explanation:

In this example, the GROUP BY clause is used to group the employees by their departments. The SUM aggregate function calculates the total salary for each department. The result set will include the "Department" column and the calculated "TotalSalary" column.

Example 2:

Let's extend the previous example and include only departments with a total salary greater than 100,000.

sql
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 100000;

Explanation:

In this example, we introduce the HAVING clause, which allows us to filter the result set based on aggregate function results. The HAVING clause is similar to the WHERE clause but operates on the grouped data. The result set will include only the departments with a total salary greater than 100,000.

Conclusion:

The GROUP BY clause in SQL is a powerful tool for grouping rows in a result set based on specified columns. It allows for the application of aggregate functions to calculate summary values within each group. By utilizing the GROUP BY clause effectively, you can obtain valuable insights and perform data analysis on your database.