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:
sqlSELECT 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.
sqlSELECT 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.
sqlSELECT 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.
0 Comments