SQL GROUP BY || DB2 for i SQL

GROUP BY clause in DB2 for i SQL

The GROUP BY clause allows us to group identical rows based upon one or more column or expressions and displays a single row for each group.

How do we use GROUP BY clause in SQL statement

GROUP BY clause is used with SQL SELECT statement.

GROUP BY clause is specified after the WHERE clause and before the ORDER BY clause if used.

and GROUP BY clause is specified before the HAVING clause if used.

If we use SQL aggregate function it will return only one row if we do not specify the GROUP BY clause and it returns more than one row when GROUP BY clause is applied as it applied to each row now. Also, when we use GROUP BY, we need to list the column or expressions that we want SQL to group the identical rows.

GROUP BY syntax

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

Create table Customer

Customer Table:

Create a table name Customers as below

CREATE TABLE Customer (
  CustId INT PRIMARY KEY,
  CustName VARCHAR(20),
  CustCity VARCHAR(20),
  CustState VARCHAR(20),
  CustCountry VARCHAR(20))

Insert records in Customer table as below to understand using GROUP BY clause in SQL queries after that

INSERT query:

INSERT INTO Customer (CustId, CustName, CustCity, CustState, CustCountry) VALUES
(1, 'Amit', 'PATNA', 'BIHAR', 'INDIA'),
(2, 'Anil', 'PATNA', 'BIHAR', 'INDIA'),
(3, 'Dhanraj', 'HISAR', 'HARYANA', 'INDIA'),
(4, 'Udeep', 'KOCHI', 'KERELA', 'INDIA'),
(5, 'Yatin', 'IDUKKI', 'KERALA', 'INDIA'),
(6, 'Tinku', 'AGRA', 'UTTAR PRADESH', 'INDIA'),
(7, 'Mounish', 'KOLKATA', 'WEST BENGAL', 'INDIA'),
(8, 'Annamalai', 'CHENNAI', 'TAMIL NADU', 'INDIA'),
(9, 'Kannan', 'CHENNAI', 'TAMIL NADU', 'INDIA')

Customer Table data:

CUSTID   CUSTNAME              CUSTCITY              CUSTSTATE             CUSTCOUNTRY 
     1   Amit                  PATNA                 BIHAR                 INDIA       
     2   Anil                  PATNA                 BIHAR                 INDIA       
     3   Dhanraj               HISAR                 HARYANA               INDIA       
     4   Udeep                 KOCHI                 KERELA                INDIA       
     5   Yatin                 IDUKKI                KERALA                INDIA       
     6   Tinku                 AGRA                  UTTAR PRADESH         INDIA       
     7   Mounish               KOLKATA               WEST BENGAL           INDIA       
     8   Annamalai             CHENNAI               TAMIL NADU            INDIA       
     9   Kannan                CHENNAI               TAMIL NADU            INDIA       

Example 1: List the number of customers in each city in country INDIA.

SELECT CustCity, COUNT(CustId) Number_Of_Customers 
FROM Customer                                      
WHERE CustCountry = 'INDIA'                        
GROUP BY CustCity                                  

Output:

CUSTCITY              NUMBER_OF_CUSTOMERS
AGRA                                 1   
PATNA                                2   
HISAR                                1   
IDUKKI                               1   
KOLKATA                              1   
CHENNAI                              2   
KOCHI                                1   

Example 2: List the number of customers in each city in country INDIA, sorted high to low.

SELECT CustCity, COUNT(CustId) Number_Of_Customers 
FROM Customer                                      
WHERE CustCountry = 'INDIA'                        
GROUP BY CustCity 
ORDER BY COUNT(CustId) DESC

Output:

CUSTCITY              NUMBER_OF_CUSTOMERS
CHENNAI                              2   
PATNA                                2   
KOCHI                                1   
AGRA                                 1   
KOLKATA                              1   
IDUKKI                               1   
HISAR                                1   

Example 3: List the number of customers in each city and state in country INDIA (Group By Multiple columns)

SELECT CustState, CustCity, COUNT(CustId) Number_Of_Customers
FROM Customer                                                
WHERE CustCountry = 'INDIA'                                  
GROUP BY CustState, CustCity                                 

Output:

CUSTSTATE             CUSTCITY              NUMBER_OF_CUSTOMERS
KERELA                KOCHI                                1   
HARYANA               HISAR                                1   
BIHAR                 PATNA                                2   
KERALA                IDUKKI                               1   
WEST BENGAL           KOLKATA                              1   
TAMIL NADU            CHENNAI                              2   
UTTAR PRADESH         AGRA                                 1