The GROUP BY clause is an essential part of SQL queries which allows the grouping of rows based on the values of one or more columns. The primary purpose of the GROUP BY clause is to aggregate data by applying various aggregate functions such as SUM, AVG, MAX, MIN, COUNT to the grouped rows.
The syntax of the GROUP BY clause is as follows:
SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2;In this syntax, the SELECT statement lists the columns we want to retrieve, and the GROUP BY clause specifies the columns that we want to use for grouping the data. The aggregate function operates on the column, which is not included in the GROUP BY clause.
For example, let’s consider a table "Orders" with the following columns: OrderID, CustomerID, OrderDate, OrderAmount. To find the total revenue generated by each customer, we can use the following query:
SELECT CustomerID, SUM(OrderAmount) AS TotalRevenue
FROM Orders
GROUP BY CustomerID;This query uses the SUM() function to add up all the order amounts for each customer, and the GROUP BY clause groups the rows based on the CustomerID column.
Another example is finding the number of orders placed by each customer in a given time period. To do this, we can use the COUNT() function as follows:
SELECT CustomerID, COUNT(OrderID) AS TotalOrders
FROM Orders
WHERE OrderDate BETWEEN '2021-01-01' AND '2021-03-31'
GROUP BY CustomerID;This query counts the number of orders placed by each customer between 1st Jan and 31st March 2021, and groups the rows based on the CustomerID column.
In summary, the GROUP BY clause is used to group rows based on one or more columns and apply aggregate functions to generate summary information for each group.