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]


0 comments to "DB2 - GROUP BY - HAVING"

Post a Comment

Who ever writes Inappropriate/Vulgar comments to context, generally want to be anonymous …So I hope U r not the one like that?
For lazy logs u can at least use Name/URL option which don’t even require any sign-in, good thing is that it can accept your lovely nick name also and URL is not mandatory too.
Thanks for your patience
~Krishna(I love "Transparency")
వీలయితే నాల్గు పోస్టులు ...కుదురితే ఒక కామెంటూ ...

Translate

Enter your email address:

Buffs ...

Visitors


hits counter
View My StatsCheck Google Page Rank

Add to Google Reader or Homepage

Bookmark and Share

Tags


Powered by WidgetsForFree