Computing the % of Total for a column

• Bryan P. Clauss

  SSC Eights!

  Points: 959

  More actions

  January 9, 2006 at 1:06 pm

  #364634

  I want to compute the percent of the total that a row has.

   

  Example:

  Name	Sales	% of Sales
  Bob	20	20%
  Frank	10	10%
  Charlie	60	60%
  Sally	4	4%
  Jane	6	6%
  Total	100	100%

  I am going to display this table in a web page, so I will be summing my totals using code, but the piece that I really want SQL to do is to compute the % column.  (I can loop through all of my returned records, total my column, then start back at the beginning to write the records out, but that seems like "cheating" to me.)

  To complicate matters, the "Name" field is actually a "Group By Department" statement that I am doing in the code.

  I was taking a quick lok at the "Compute" and "Compute By" statements in SQL help.  This seems like a cool feature to do Grouping on, displaying all of my details, breaking on certain fields, etc... 

  Can someone give me a quick and dirty sample of how to set one up?

  Thanks,

  Bryan Clauss

• noeld

  SSC Guru

  Points: 96590

  More actions

  January 9, 2006 at 1:42 pm

  #613672

  This is what I would do

   

  Select Name, Sales, round(100*Sales/TotalSales,0) [% os Sales]

  from

  (Select Department as Name, Sum(Amount) as Sales

  from SalesTable

  Group by Department ) dt1

  cross join (Select Sum(Amount) as TotalSales from SalesTable ) t

  

  
  * Noel