How to calculate percentages using the Aggregate task

• Isabelle2378

  SSCertifiable

  Points: 6174

  More actions

  February 28, 2007 at 10:11 am

  #116462

  Hi,

  This seems like it should be a simple task but I can't figure out why mine is not working.  I have a column QTY and I'm trying to calcuate the % for each column with the following code:  QTY / SUM of QTY * 100.  So I created an aggregate task and chose the column QTY and operation SUM and called the output SUM OF QTY and I was under the impression that it would sum up all the values of that column.  I put a data viewer on it to see what was coming out because my percentages were wrong and it is not calculating the sum.  Instead the SUM OF QTY field has the same data as the QTY field.

  What am I doing wrong?  Is there an easier way to do this.

  Thanks in advance!

  Isabelle 

  Thanks!
  Bea Isabelle

• Leonce

  SSC Enthusiast

  Points: 198

  More actions

  February 28, 2007 at 2:55 pm

  #692440

  I hit that kind of problem once.

  Let's say you have a list of value you want to compute the ratios:

  id - qty

  1, 10

  2, 20

  3, 30

  4, 40

  You want to calculate the ratio for each, which should be:

  1 = 10%

  2 = 20%

  3 = 30%

  4 = 40%

  It's pretty obvious, but to compute the ratio for a row you actually needed to know the values of the other rows (to get the total quantity).  That means the ratios cannot be computed in a single step.

  I would try to calculate them using plain SQL.

  Something like:

  SELECT id, qty, qty/TotalQty as ratio

  FROM Table CROSS JOIN (SELECT SUM (qty) AS TotalQty FROM Table) Total

  If you have groups for which you want to compute avg it would look like this:

  SELECT

  id, qty/totalGrp AS ratio

  FROM dbo.theSumGroup T1

  INNER JOIN (SELECT group_id, Sum(qty) AS totalGrp FROM dbo.theSumGroup GROUP BY group_id) T2

  ON T1.group_id = T2.group_id

  EDIT:

  The SSIS way would be to put the totalQty in a variable using the Aggregate and use that variable in the SQL statement.

  Hope it helps

  Leonce