Talend Aggregate Sorted Row

The Talend Aggregate Sorted row or tAggregateSortedRow field is the same as the tAggreagteRow. However, it performs Aggregations on sorted data and returns the output. We can use this Talend Aggregate Sorted Row field if we know that the source is sorted. Because Talend tAggregateSortedRow does not maintain any buffer, it is faster than the tAggreateRow.

In this Talend Aggregate sorted row example, we used the same Customer table that we used in the Aggregate row example. The below screenshot shows the data inside that table.

Customer Sales Table with HireDate

Talend Aggregate Sorted Row Example

First, drag and drop the DBInput and choose the Customers table from the metadata. Please refer to the tAggregateRow article to understand the connection settings.

Talend Aggregate Sorted Row 2

Next, drag the Talend tAggregateSortedRow from the palette to the job design. By selecting the field, you can see the tooltip defining the tAggregateSortedRow.

Talend Aggregate Sorted Row 3

Please connect the DBInput main row to Talend tAggregateSortedRow. We already explained all the options in the previous example. Apart from them, it has one more field in the Component tab called Input rows count. Here, you have to specify the total number of rows coming from the input.

Talend Aggregate Sorted Row 4

Next, click the Edit Schema button to add the required columns to the tAggregateSortedRow. Here, we are adding Occupation, Yearly Income, and Sales columns.

Talend Aggregate Sorted Row 5

For the number of Input rows, you can manually type the number or select the Number of lines (NB_Line) from the outline. Here, we accidentally added the NB_LINE of the tAggregateSortedRow, but you have to add the DBInput NB_LINE. We changed it later and forgot to take that screenshot.

Please use the plus button to add the group by columns. Here, we added Occupation columns for grouping. Next, we added Yearly Income and Sales in the Operations section and selected the Sum function.

It means we are grouping Customers by Occupation and finding the Sum of Yearly Income, and Sales.

Talend Aggregate Sorted Row 7

Here, we are using the DBOutput to save the Talend Aggregate Sorted Row output in SQL Database. Here, we are creating the Talend_tAggSortedRow table as the destination table.

Talend Aggregate Sorted Row 8

Let us run the Talend Aggregate Sorted row Job.

Talend Aggregate Sorted Row 9

Let me open the Management studio to check the tAggregateSortedRow result.

Two compare the result; we also wrote a T-SQL query on the Customer table. As you can see from the below screenshot, both are returning different results. It is because Talend tAggregateSortedRow assumes that the data is sorted.

Talend Aggregate Sorted Row 10

Let us sort the data and run this Talend Aggregate Sorted Row. To do so, let me drag and drop the tSortRow field in the middle.

Here, we used the tSortRow field to sort the data based on the Occupation in ascending order.

Talend Aggregate Sorted Row 12

Next, we connected this tSortRow with Talend tAggregateSortedRow, kept the remaining setting as it is, and ran the job.

Talend Aggregate Sorted Row 13

Please check the Talend aggregate sorted row result.

Talend Aggregate Sorted Row 14

This time, we are using the sorted data as the DBInput source data. For this example, we changed the input table from Customers to Talend_Sorted_Customers. It is the output table that we got from the Talend Sort Data article.

Talend Aggregate Sorted Row 15

Please keep the Grouping and aggregate functions as it is or, as shown below.

Talend Aggregate Sorted Row 16

Let us run this Talend Aggregate Sorted Row job.

Talend Aggregate Sorted Row 17

The result of the Talend tAggregateSortedRow job is

Talend Aggregate Sorted Row 18