DB2 - SQL GROUP BY Statement

The GROUP BY statement is used to grouping the rows that have the same values in the table.

Grouping lets you divide data into logical sets so that you can perform aggregate calculations on each group. Groups are created using the GROUP BY clause in the SELECT statement.

The GROUP BY clause instructs the DB2 to group the data and then perform the aggregate on each group rather than on the entire result set.

Syntax:

SELECT column_name(s)
  FROM table_name
  WHERE condition
  GROUP BY column_name(s);

DB2 Database:

Below is a selection from the "Product" table in the DB2 database.

Example 1:

The following SQL statement lists the number of different Category in "Product" table.

SELECT Category, COUNT(Category) As "Number_of_Products"
    FROM Product
    GROUP BY Category;

The GROUP BY clause instructs DB2 to sort the data and group it by Category. This causes "Number_of_Products" to be calculated once per Category rather than once for the entire table.

The result of this query is as follows.

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!