{"id":338,"date":"2019-04-07T01:32:19","date_gmt":"2019-04-07T09:32:19","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=338"},"modified":"2020-04-11T06:59:36","modified_gmt":"2020-04-11T14:59:36","slug":"db2-group-by","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-group-by\/","title":{"rendered":"Db2 GROUP BY"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 GROUP BY<\/code> clause to group rows into groups.<\/p>\n\n\n\n

Introduction to Db2 GROUP BY clause<\/h2>\n\n\n\n

When you use the SELECT<\/a><\/code> statement to query data, you get a result set which consists of rows. To divide these rows into groups, you use the GROUP BY<\/code> clause as shown in the following query:<\/p>\n\n\n

SELECT<\/span>\n    select_list\nFROM<\/span>\n    table_name\nGROUP<\/span> BY<\/span>\n    column1, column2,...;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This statement divides rows derived from the FROM<\/code> clause into groups by one or more column expression (column1<\/code>, column2<\/code>, …) specified in the GROUP BY<\/code> clause.<\/p>\n\n\n\n
An aggregate function takes multiple rows as an input and returns a single value for these rows. Some commonly used aggregate functions are AVG()<\/a><\/code>, COUNT()<\/a><\/code>, MIN()<\/a><\/code>, MAX()<\/a><\/code> and SUM()<\/a><\/code>. For example, the COUNT()<\/code> function returns the number of rows for each group. The AVG()<\/code> function returns the average value of all values in the group.<\/p>\n\n\n\n
Here is the common query that uses the GROUP BY<\/code> clause with an aggregate function:<\/p>\n\n\n
SELECT<\/span>\n    column1, \n    column2, \n    ...\n    aggregate_function(expression)\n\nFROM<\/span>\n    table_name\nGROUP<\/span> BY<\/span>\n    column1,\n    column2,\n    ...;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that any column listed in the select list that is not in the aggregate function expression must be placed in the GROUP BY<\/code> clause, or you will get an error.<\/p>\n\n\n\n
Db2 GROUP BY clause examples<\/h2>\n\n\n\n
Let’s use the books<\/code> and publishers<\/code> tables from the sample database<\/a> for the demonstration.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
1) Using Db2 GROUP BY clause with COUNT(*) function<\/h3>\n\n\n\n
This statement uses the GROUP BY<\/code> clause with the COUNT(*)<\/code><\/a> function to find the number of books by publishers:<\/p>\n\n\n
SELECT<\/span> \n    publisher_id, \n    COUNT<\/span>(*) book_count\nFROM<\/span> \n    books\nGROUP<\/span> BY<\/span> \n    publisher_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"db2<\/figure>\n\n\n\n
In this statement:<\/p>\n\n\n\n
When selecting groups of rows from the database, we are interested in the characteristics of the groups, not individual rows. Therefore, we often use aggregate functions<\/a> in conjunction with the GROUP BY<\/code> clause.<\/p>\n\n\n\n