Calculating a percentage using aggregate task

Hi,

I have a column called QTY and I need to calculate the percentage for each row based on the total sum of the column. They are all decimals. I have an OLE DB source that is reading the table used an aggregate task using the QTY column with operation SUM and called the output SUM OF QTY. Then I have a Derived Column task that takes the QTY column / SUM OF QTY * 100 to create the percentage. My numbers are not coming out correctly and when I put a data viewer from the aggregate task, it shows the same number for both columns:

QTY SUM OF QTY

0.01833 0.01833 (this should be the sum of all the decimals in the QTY col)

What am I doing wrong?

Thanks in advance!

Isabelle

You might have to use a multitask component to get this to work. The aggregate transformation does not pass fields through. So from the multitask output, you can take one path to the aggregate, and another to a sort. Take the output of the aggregate and put it in a sort as well. Then, take the two sorts and hook them up with a merge join on the common keys. Then, you'll have your original data, along with the aggregate column.|||

Thanks for your response. I tried what you suggested and I still could not get it to work. Even when I created a sort column for both and then used a join merge, I still got the same amound in both columns. I ended up doing the conversion using Execute SQL task and saved the output as a variable and then just called the variable in a derived column and that worked.

Isabelle