SQL HAVING || DB2 for i SQL

HAVING clause in DB2 for i SQL

The HAVING clause is used to specify the search condition for the groups selected by the GROUP BY clause. Means we want only those groups that satisfy the condition in HAVING clause.

In simple words, I would say that HAVING keyword is used just because WHERE clause cannot be used with the aggregate functions.

HAVING clause is specified just after GROUP BY clause.

Syntax of HAVING clause

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

Create table Customer

We should use the existing table named Customer for understanding this clause.

Example 1: List the number of customers in each city in country INDIA. Only include city's with more than 1 customers.

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

Output:

CUSTCITY              NUMBER_OF_CUSTOMERS
CHENNAI                              2   
PATNA                                2   

Example 2: List the number of customers in each city in country INDIA. Only include city's with at least 1 customer and sorted high to low.

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

Output:

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