How do I calculate percentage??

• Nuts

  SSCrazy

  Points: 2547

  More actions

  August 23, 2007 at 10:04 pm

  #176137

  Hi Guys

  I need to calculate the percentage in a new column and I have posted the samle data

  The percentage will be calculate by (colD/colB)*100

   

  And I need to have the '%' symbol along with the result

  ex 17%, 30% etc

  ColA  Colb          colC         colD         Percentage(?)

  D	121936	114957	6979	0
  H	235755	215957	19798	0
  P	2416	29608	2808	0
  R	519992	488069	31923	0

   

   

  Thanks guys

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  August 23, 2007 at 10:41 pm

  #729167

  Your best bet that will give you the greatest flexibility would to do formatting on the presentation layer (whatever you use to display to the user, ie crystal, MS reporting services, HTML, access, excel, whatever.)

  Below will give you the percentage

  Create table #Mytable (ColA char(1), colB int, colC int, colD int)

  insert into #Mytable (colA, colB, colC, colD)

  values ('D',  121936,   114957,   6979)

  insert into #Mytable (colA, colB, colC, colD)

  values('H', 235755,  215957,   19798)

  insert into #Mytable (colA, colB, colC, colD)

  values('P', 2416,   29608,   2808)

  insert into #Mytable (colA, colB, colC, colD)

  values('R', 519992,   488069,   31923 )

  select colA, colD / cast(colB as decimal(8,2))

  from #Mytable

  Results

  D .057234942

  H .083977010

  P 1.162251655

  R .061391329

  The following will place the % at the end, Notice the additional code required.

  and I suspect you will have trouble getting it to round how you want it and such.

  select colA, convert(varchar(25), cast((colD / cast(colB as decimal(8,2))) as decimal(8,3))*100) + ' %'

  from #Mytable

  Results

  D 5.700 %

  H 8.400 %

  P 116.200 %

  R 6.100 %

• Jeff Moden

  SSC Guru

  Points: 1004361

  More actions

  August 23, 2007 at 11:57 pm

  #729179

  I agree with Ray... this type of formatting is better done in the GUI if you have one.

  Also, the reason why you were getting zeros for your percentage was because it was all INTERGER math... Ray showed one way using CONVERT... you can actually do it even easier just by adding a .0 to a strategically placed time 100.0...

  Create table #Mytable (ColA char(1), colB int, colC int, colD int)

  insert into #Mytable (colA, colB, colC, colD)

  values ('D',  121936,   114957,   6979)

  insert into #Mytable (colA, colB, colC, colD)

  values('H', 235755,  215957,   19798)

  insert into #Mytable (colA, colB, colC, colD)

  values('P', 2416,   29608,   2808)

  insert into #Mytable (colA, colB, colC, colD)

  values('R', 519992,   488069,   31923 )

  --===== This will also align the decimal points

  select colA, STR(colD * 100.0 / colB,10,3)+'%'

  from #Mytable

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)