The SQL GROUP BY Statement
The SQL GROUP BY statement is used to group the rows that have the same values in one or more columns into summary rows in SQL. It allows you to perform aggregate functions like COUNT, SUM, AVG, MAX, or MIN on these grouped rows.
OR
The SQL Group by statement is used to organize the same data into groups with the help of aggregate functions like COUNT, SUM, AVG, MAX, or MIN on these grouped rows.
Here, we will learn SQL GROUP BY statement with examples.
Syntax : SQL GROUP BY Statement
SELECT column1, column2, column3,…
FROM tablename
GROUP BY columnA, columnB,…;
column1, column2, column3… Table’s columns
columnA, columnB … column(s) based on that have the same values.
Examples of SQL GROUP BY Statement
Example 1: Simple sql GROUP BY
Suppose you have a table called Orders with columns CustomerID and TotalAmount and you want to find the total number of orders placed by each customer. You can use the GROUP BY statement as follows:
SQL> SELECT CustomerID, SUM(TotalAmount) AS TotalOrderAmount
FROM Orders
GROUP BY CustomerID;
GROUP BY CustomerID groups the rows in the Orders table based on the CustomerID column.
SUM(TotalAmount) calculates the total order amount for each group.
Example 2: GROUP BY with Multiple Columns.
You can use the GROUP BY statement with multiple columns to create more specific groups. For example, if you want to find the total order amount for each customer in each year:
SQL> SELECT CustomerID, YEAR(OrderDate) AS OrderYear, SUM(TotalAmount) AS TotalOrderAmount
FROM Orders
GROUP BY CustomerID, YEAR(OrderDate);
GROUP BY CustomerID, YEAR(OrderDate) groups the rows based on both CustomerID and the year extracted from the OrderDate column. SUM(TotalAmount) calculates the total order amount for each combination of customer and year.
Example 3: Using Aggregate Functions with sql GROUP BY
The GROUP BY statement is often used in combination with aggregate functions like COUNT, AVG, MAX, or MIN. For instance, if you want to find the average order amount for each customer:
SQL> SELECT CustomerID, AVG(TotalAmount) AS AverageOrderAmount
FROM Orders
GROUP BY CustomerID;
AVG(TotalAmount) calculates the average order amount for each customer group.
Example 4: SQL GROUP BY with HAVING Clause
You can use the HAVING clause in combination with sql GROUP BY to filter groups based on aggregate function results. For example, if you want to find customers who have placed more than three orders:
SQL> SELECT CustomerID, COUNT(OrderID) AS TotalOrders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 3;
GROUP BY CustomerID groups the rows based on CustomerID.
COUNT(OrderID) calculates the total number of orders for each customer group.
HAVING COUNT(OrderID) > 3 filters the groups, showing only those with more than three orders.
In order to create summary statistics and execute aggregations in SQL, the sql GROUP BY statement is necessary. You can use aggregate functions on the groupings of data you create based on certain criteria, which enables you to extract insightful information from your database.