SQL - Select Group By

The GROUP BY clause in SQL is used to group rows based on one or more columns. It allows you to perform aggregate functions on groups of rows rather than on individual rows. The GROUP BY clause is often used in combination with aggregate functions like SUM, COUNT, AVG, etc., to generate summary reports or perform calculations on subsets of data.

Syntax:

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

Example:

Let's assume we have a "Sales" table with the following columns:

ProductID (unique identifier for each product)

CategoryID (category identifier for the product)

Quantity (quantity sold)

Price (price per unit)

To calculate the total quantity sold for each category, we can use the GROUP BY clause with the SUM aggregate function:

SELECT CategoryID, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY CategoryID;

Explanation:

The SELECT statement specifies the columns we want to retrieve from the table. In this case, we select CategoryID and use the SUM aggregate function to calculate the total quantity sold for each category.

The FROM clause indicates the table from which we want to retrieve data. In this example, it's the "Sales" table.

The GROUP BY clause is used to group the rows based on the CategoryID column. This means that the SUM function will be applied to each group separately.

The SUM(Quantity) function calculates the sum of the Quantity values for each category group.

The AS TotalQuantity statement provides an alias for the calculated sum, allowing us to refer to it as "TotalQuantity" in the query result.

By executing this query, you will retrieve the CategoryID and the total quantity sold for each category. The result will have one row for each unique value in the CategoryID column, with the corresponding sum of quantities. Adjust the table name, column names, and the aggregate function as per your specific database schema and requirements. The GROUP BY clause is useful when you want to summarize or analyze data based on specific groups or categories.

Example : Counting the Number of Orders per Customer:

Let's assume we have an "Orders" table with the following columns:

OrderID (unique identifier for each order)

CustomerID (unique identifier for each customer)

OrderDate (date when the order was placed)

To count the number of orders placed by each customer, we can use the GROUP BY clause with the COUNT aggregate function:

SELECT CustomerID, COUNT(OrderID) AS OrderCount
FROM Orders
GROUP BY CustomerID;

This query will retrieve the CustomerID and the count of orders (OrderCount) for each customer.

Example : Calculating the Average Salary per Department:

Assuming we have an "Employees" table with the following columns:

EmployeeID (unique identifier for each employee)

DepartmentID (unique identifier for each department)

Salary (salary of the employee)

To calculate the average salary for each department, we can use the GROUP BY clause with the AVG aggregate function:

SELECT DepartmentID, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DepartmentID;

This query will retrieve the DepartmentID and the average salary (AverageSalary) for each department.

Example : Finding the Maximum Order Amount by Country:

Assuming we have an "Customers" table with the following columns:

CustomerID (unique identifier for each customer)

Country (country of the customer)

OrderAmount (amount of an order placed by the customer)

To find the maximum order amount for each country, we can use the GROUP BY clause with the MAX aggregate function:

SELECT Country, MAX(OrderAmount) AS MaxOrderAmount
FROM Customers
GROUP BY Country;

This query will retrieve the Country and the maximum order amount (MaxOrderAmount) for each country.

These examples demonstrate how the GROUP BY clause can be used in combination with different aggregate functions to summarize data based on specific groups or categories. Adjust the table names, column names, and the aggregate functions as per your specific database schema and requirements.