[resolved] How to compute weighted average in Talend

[Anonymous]

Hello.
I need to compute weighted average at product leve basedon units and price.
Here is sample table:
ProdurctID Units Price
222 10 $15
333 15 $20
444 37 $5

It is very easy in SQL:
SELECT ProdurctID, SUM(units * price) / SUM(units) AS wavg FROM table GROUP BY ProdurctID;
or
SELECT ProdurctID, SUM(units * price) / SUM(units) over (partition by product) AS wavg FROM table GROUP BY ProdurctID;
Please explain how to implement it in Talend as I the tool does not let me use SQL functions.
I use the following components:
tMysqlInput -> tMap -> tAggregateRow -> tMysqlOutput
Thank you,
Pit.

[Anonymous]

If you are using a DB as your source, you can simply use SQL to calculate these averages in your input components.
if you have a file, I would use a tAggregateRow to compute all the SUM's for each row, and then a tmap to do the division.

[Anonymous]

What component should I use to calculate all in SQL? I don't see any references to SQL in tMySQLOutput or Input.

[Anonymous]

Any ideas, folks?

[Anonymous · Accepted solution]

Peter,
You can put custom queries into a tMysqlInput component in the "Query" text box. Just remember that when you write a query manually, you must define the schema manually as well. The schema is positional, so column 1 in the query needs to be schema element 1.

[Anonymous]

Thanks a lot!