Updated March 6, 2023
Introduction to DB2 GROUP BY
DB2 GROUP BY clause helps us to get the collective accumulated and grouped data in Relational databases like DB2 RDBMS. Whenever we retrieve the data from the table(s), we get multiple rows that represent themselves individually. But many times, there is a necessity to get the grouped or collective information from the raw data. At that time, we can make the groups of all the retrieved rows present in the table based on one or more column values or parameters which can be further used to get various types of aggregate values using aggregate functions like SUM(), MAX(), MIN(), COUNT() and AVG() functions along with the GROUP BY clause in it. In this article, we will have a look at the usage of the GROUP BY clause, its syntax and how it can be used with number of aggregate functions with the help of certain examples.
Syntax
SELECT
Values to be retrieved
FROM
Name of the table
GROUP BY
Column_name1, [ column_name2, ... ];
In the above syntax, values to be retrieved can contain any aggregate function used on one or more columns and many such functions can be used. The name of the table is the table name that is present in our database. The column name is the name of the column on which we have to group the rows of the table.
We can group the data of the tables based on one or more than one column names that belong to a particular table. Note that if we select the column name values to be retrieved in the answer then we will get only one row which might be incorrect as it is not a cumulative result. In order to get the collective result, one should make the use of the aggregate functions in selection of the data. As most of the times, we use thee group by statements in order to create the groups and get the collective grouped and summarized data and information from it, we use aggregate functions along with group by clause. Let us have a look at the implementation of GROUP BY clause with different aggregate functions with the help of some examples. Suppose that we have a table named Sales_Customers that stores the data related to sale and the details of the bills and customers corresponding to it. Let us retrieve the contents of the table and have a look at it by using the following select query statement –
SELECT * FROM [Sales_Customers];
The execution of above query statement gives following result as output –
Suppose that we want to group the data based on the month of the sale of those transactions and derive the monthly total sales. In this case, we will need to group our data of the table based on the store_id column which can be retrieved by using store_id. Further, to get the total sales of that particular store, we will have to use the SUM() aggregate function on bill_amount column. Hence, our query statement will now look like following –
SELECT store_id, SUM(bill_amount) FROM [Sales_Customers] GROUP BY store_id;
The output of above query statement is as shown below with all the store names and the total bill amount for each store which gives the sales details store wise –
Now, let us calculate the average sale for each store to see the amount of the sale that is being made averagely for each of the store name details such as fruits section, groceries, daily essentials, electronics and vegetables. To get the average value our query statement will contain AVG() function instead of S
UM() while GROUP BY clause will contain the same column of store_id value. Hence, our query statement will become as shown below –
SELECT store_id as 'Store Section', AVG(bill_amount) as 'Bill Amount' FROM [Sales_Customers] GROUP BY store_id;
The output of the above query statement gives the following result along with the average value of the bill amount and the name of the store section corresponding to it as shown below –
let us calculate the maximum sale at each store to see the amount of the sale that is being made maximumly for each of the store name details such as fruits section, groceries, daily essentials, electronics and vegetables. To get the maximum value our query statement will contain MAX () function instead of AVG() while GROUP BY clause will contain the same column of store_id value. Hence, our query statement will become as shown below –
SELECT store_id as 'Store Section', MAX(bill_amount) as 'Max Bill Amount' FROM [Sales_Customers] GROUP BY store_id;
The output of the above query statement gives the following result along with the maximum value of the bill amount and the name of the store section corresponding to it as shown below –
let us calculate the minimum sale at each store to see the amount of the sale that is being made minimum for each of the store name details such as fruits section, groceries, daily essentials, electronics and vegetables. To get the minimum value our query statement will contain MIN () function instead of AVG() while GROUP BY clause will contain the same column of store_id value. Hence, our query statement will become as shown below –
SELECT store_id as 'Store Section', MIN(bill_amount) as 'Min Bill Amount' FROM [Sales_Customers] GROUP BY store_id;
The output of the above query statement gives the following result along with the minimum value of the bill amount and the name of the store section corresponding to it as shown below –
Now, in order to the calculate number of bills at each store section, we will need to use the COUNT average function on bill_amount along with GROUP BY clause on store_id column, our query statement will become as follows –
SELECT store_id as 'Store Section', COUNT(bill_amount) as 'Number of Bills' FROM [Sales_Customers] GROUP BY store_id;
The output of the above query statement gives the following result along with the count of the bill amount and the name of the store section corresponding to it as shown below –
Conclusion
We can make the use of GROUP BY clause in DB2 to make the groups and divide the rows of the table in groups depending on one or more than one column of the table and mostly used along with aggregate functions.
Recommended Articles
This is a guide to DB2 GROUP BY. Here we discuss the usage of the GROUP BY clause, its syntax, and how it can be used with the number of aggregate functions with the help of certain examples. You may also look at the following article to learn more –
