topic Re: Group data by a column and then sum multiple columns for each group in Talend Studio

Group data by a column and then sum multiple columns for each group

_AnonymousUser — Sat, 16 Nov 2024 13:56:44 GMT

I've got a CSV with the following headers
Date|Name|Type|Status|Gross|Fee|Net|Shipping|Balance
What I'm trying to do is group the data by the Type column (Payment, Refund, Declined) and then sum the Gross, Fee, Net, & Shipping columns for each group. Currently my work flow is as follows
tFileInputDelimited->tSortRow(sort by column Type)->tMap(removing unnecessary columns)->tFileOutputDelimited
I see that the tAggregateSortedRow function provides the ability to group by and sort but it looks like it's only for databases. Is there a way to preform this operation on a CSV file?
Cheers,
AJ

Re: Group data by a column and then sum multiple columns for each group

Anonymous — Fri, 22 May 2009 23:03:24 GMT

tAggregateRow (and tAggregateSortedRow ) work on Talend rows-- the data can come from any input type. Are you having trouble getting these components to work for your need?

Re: Group data by a column and then sum multiple columns for each group

Anonymous — Fri, 03 Jan 2014 07:36:07 GMT

i have a mysql table with the columns
date|customer_name|purchase_amount|
i want to group the data based on the customer name and date.
and i want to load this data to another table of the same database with the grouped result and it should be update on the basis of current date.
so,please help me i am new in this technology...
Regards,
Chandan kumar

Re: Group data by a column and then sum multiple columns for each group

Anonymous — Fri, 03 Jan 2014 08:18:40 GMT

hi,

as JohnGarrettMartin said use tAggregateRow :
https://help.talend.com/search/all?query=tAggregateRow&content-lang=en
A better practice to use Talend component dedicated to an action, but you could let the Server (DB) engine do the job and aggregate & group by in your sql statement

/** something like **/
select ...... , sum(..)
from .....
group by .....

As you have to load result in table in the same DB , you also can use ELT that have a graphic Talend way to design your job but do not extract data to manage it but let the DB engine do the request ans insert result into yout table.

hope it helps you
regards
laurent

Re: Group data by a column and then sum multiple columns for each group

Anonymous — Fri, 03 Jan 2014 09:19:35 GMT

Hi ,
Thanks for your quick response.
but i want to extract data,grouping and sum through the Talend component not through the sql query.
please assist me for the same.
Regards,
Chandan Kumar