GROUP BY - connect_cdc_sqdata - Latest

The GROUP BY command works in conjunction with the aggregate functions (i.e. SUM, MIN, MAX). This command allows you to group source records based on a field(s) in a source datastore and works in a similar manner as the SQL GROUP BY function, except that this command works against non-relational datastores as well as relational datastores.

GROUP BY [<source_datastore>][.<source_description>].<source_field>;

Example

GROUP BY SRC_DS.DEPT
PROCESS INTO TGT_DS
SELECT
{
   TGT_DS.DEPT_NAME     = CDCIN.DEPT_NAME
   TGT_DS.EMPL_COUNT   = COUNT(CDCIN.EMPL_NUM)
}
FROM CDCIN;

When this script is executed, there will be one (1) target record written for each distinct department in the source datastore. These target record will contain the department name and the count of the number of employees within the department.