EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials DB2 Tutorial DB2 GROUP BY
Secondary Sidebar
DB2 Tutorial
  • DB2 Tutorial
    • What is DB2?
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE

DB2 GROUP BY

DB2 GROUP BY

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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];

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,112 ratings)

The execution of above query statement gives following result as output –

DB2 GROUP BY output 1

DB2 GROUP BY output 2

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 –

DB2 GROUP BY output 3

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 –

DB2 GROUP BY output 4

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 –

output 5

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 –

output 6

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 –

output 7

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 –

  1. Scala groupBy
  2. Pandas DataFrame.groupby()
  3. Pandas cut()
  4. Panel Data Analysis
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more