How to implement “Group By” and “Having” clause in ODI

GROUP BY:

Group By is mainly used to group the individual records and we can divide then into groups
In ODI,if we want to implement group by  class then we have to make use of  any “Group Function”.
Group function applied only on number columns.

The different group functions or aggregate functions that are available:

·         Sum

·         Avg

·         Max

·         Min

·         Count

·         Variance

·         Standard Deviation.

 

I want to display the average salary by job wise.

This I can achieve by single SQL statement as shown in below.

 

HAVING:

After grouping data, if we want to perform filtering on the grouped records then we have to make use of the ”Having clause”.

Without “having clause” the group by can exist but the existence of having demands group by

I want to display the average salary is greater than 1500 by job wise.

ODI

Group By

New interface Give the name as TAR_AGG_EMP

Drag “EMP” To Source Pane from source models.
Drag “TRG_EMP_GROUPBY” To Target Pane from target models.

While mapping to target SAL column map with AVG(SAL) in expression editor as shown in above.

Once complete click on save.Now click on execution button

Group By implementation.

 

 

 

Now repeat all above steps to implement having clause.

Apply filter on SAL column

 Give the condition as AVG(SAL)>1500

 

 

 

Result: