SQL Group By
The GROUP BY clause in SQL is used to group rows in a result set based on one or more columns. It is often used in conjunction with aggregate functions such as SUM, COUNT, AVG, etc., to perform calculations on each group of rows.
The basic syntax for using GROUP BY in SQL is as follows:
SELECT column1, column2, ..., aggregate_function(column) FROM table WHERE conditions GROUP BY column1, column2, ...
Here’s a breakdown of the components:
SELECT: Specifies the columns to be included in the result set.
aggregate_function(column): Applies an aggregate function to a specific column, such as SUM, COUNT, AVG, MAX, MIN, etc. This function performs calculations on the grouped values.
FROM: Specifies the table or tables from which to retrieve data.
WHERE: Optional clause to specify conditions that filter the rows.
GROUP BY: Specifies the columns to group the result set by.
Here’s an example to illustrate the usage of GROUP BY:
Consider a table named “Orders” with columns: OrderID, CustomerID, Product, and Quantity.
SELECT CustomerID, SUM(Quantity) AS TotalQuantity FROM Orders GROUP BY CustomerID;
This query will group the rows by CustomerID and calculate the total quantity for each customer. The result will include two columns: CustomerID and TotalQuantity.
Another example, using the “Employees” table with columns: EmployeeID, FirstName, LastName, Department, and Salary.
SELECT Department, AVG(Salary) AS AverageSalary FROM Employees GROUP BY Department;
This query groups the rows by Department and calculates the average salary for each department. The result will include two columns: Department and AverageSalary.
The GROUP BY clause allows you to perform calculations on a per-group basis, providing valuable insights and summaries from your data.