DB2 - GROUP BY - HAVING

DB2 - GROUP BY - HAVING

GROUP BY : To group different rows of data on specific coloumn name .

HAVING : on finale output of select query put we can put a condition to filter specific

Note : While "WHERE" is used to apply selection criteria to the base data, "HAVING" is used to apply selection criteria to the grouped data:

Example 1 :

TOUR_GROUP : Table has following coloumns

TOUR GUIDE LANGUAGE TOUR_DATE START_TIME END_TIME GROUP_SIZE AVAILABILITY

Query 1 :

SELECT LANGUAGE, COUNT(*) "NUMBER_OF_TOURS", MAX(GROUP_SIZE) "MAX_GROUP_SIZE", MIN(GROUP_SIZE) "MIN_GROUP_SIZE"
FROM TOUR_GROUP
GROUP BY LANGUAGE
ORDER BY NUMBER_OF_TOURS;

Query 2 :

SELECT LANGUAGE, COUNT(*) "NUMBER_OF_TOURS", MAX(GROUP_SIZE) "MAX_GROUP_SIZE", MIN(GROUP_SIZE) "MIN_GROUP_SIZE"
FROM TOUR_GROUP
WHERE GROUP_SIZE <= 20
GROUP BY LANGUAGE
HAVING COUNT(*) > 1
ORDER BY NUMBER_OF_TOURS;

Query 3 :

SELECT TOUR, COUNT (DISTINCT LANGUAGE) "NUMBER_LANGUAGES"
FROM TOUR_GROUP
GROUP BY TOUR
ORDER BY COUNT(DISTINCT LANGUAGE), TOUR;

Reference : [1] [2]