How to Group and Aggregate Data Using SQL?

In SQL, grouping and aggregating data are essential techniques for analyzing datasets. When dealing with large volumes of data, we often need to summarize or categorize it into meaningful groups. The combination of the GROUP BY clause and aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() makes it easy to perform such tasks.

In this article, we will explain how to group and aggregate data in SQL to make our queries more efficient and insightful.

Grouping and Aggregating Data

Grouping Data: The GROUP BY clause in SQL is used to arrange identical data into groups based on one or more columns. Once data is grouped, aggregate functions can be used to summarize the information within those groups.

Aggregating Data: Aggregate functions perform calculations on multiple rows of data and return a single result. Common aggregate functions include COUNT(), SUM(), AVG(), MIN(), and MAX(). These help us get summaries like totals, averages, and counts for different categories or groups

Syntax:

SELECT column1, column2, ..., AGGREGATE_FUNCTION(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...
HAVING condition
ORDER BY column1;

Example 1: Grouping Data and Counting Occurrences

Let's consider the Sales_Order table in a Sales database. This table stores information about sales orders, including the client number, order date, and order status.The Sales_Order table contains details about orders made by clients, including their order status.

Let’s say we want to count how many orders are still "In Process" for each client. To do this, we use the COUNT() function along with GROUP BY

Query:

SELECT CLIENTNO, COUNT(ORDERSTATUS)
AS Order_In_Process
from Sales_Order WHERE 
ORDERSTATUS='In Process' GROUP BY CLIENTNO; 

Output:

Explanation:

In this case, we grouped the data by CLIENTNO and counted how many orders are in the "In Process" status for each client. From this output, we can easily see that clients C00001 and C00006 each have one order that is still "In Process".

Example 2: Summing Values in Groups

Now, let's move to another table called Ordered. This table contains sales data for different places, where each sale is associated with a location and a price. The Ordered table stores data on products sold, including the location and price of each item sold.

Let’s say we want to calculate the total revenue from sales in different places. we will use the SUM() function along with the GROUP BY clause to calculate the total sales price for each place.

Query:

SELECT place, SUM(price) AS Total_Revenue
FROM Ordered
GROUP BY place;

Output:

Example 3: Using Multiple Aggregate Functions

SQL allows us to use multiple aggregate functions in a single query. This can provide a more comprehensive view of your data. In this example, we will use the AVG(), MAX(), and MIN() functions to calculate the average, maximum, and minimum price for sales in each place.

Query:

SELECT place,
       AVG(price) AS Avg_Price,
       MAX(price) AS Max_Price,
       MIN(price) AS Min_Price
FROM Ordered
GROUP BY place;

Output:

Conclusion

Grouping and aggregating data in SQL are powerful techniques for summarizing large datasets. By using the GROUP BY clause along with aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX(), we can efficiently analyze and summarize our data in meaningful ways. With this knowledge, we can start writing more efficient SQL queries, performing complex analyses, and gaining deeper insights from our datasets.

diyaroy22

Follow

Improve

Article Tags :

• SQL
• TrueGeek
• Databases
• TrueGeek-2021
• SQL-Server

+1 More