3

In SQL Server 2008, I am running a report where I need to divide two columns (called Completes and Prescreens, whole integers), and then I go to Excel and get the Prescreens divided by the Completes, and turn that into a number with 2 decimal places, then convert that to a percentage. i.e. -> 0.34, then becomes 34%.

In SQL in know I probably can't get the output as "34%" but at least I'd like it as 0.34 . So far I have:

cast((sq.Completes/sq.Screens ) as float ) as 'conv'

Completes are the smaller of numbers(usually 27, 38, etc), and Screens are bigger numbers(124, 276, etc.)

But this is outputting my column as all 0s . How do I make it show 0.34 , 0.84, etc

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Caffeinated
  • 11,982
  • 40
  • 122
  • 216

2 Answers2

6

Try it like this:

select (cast(sq.Completes as float)/cast(sq.Screens as float)) as 'conv'

You can also do this to round the value:

select round((cast(sq.Completes as float)/cast(sq.Screens as float)),2) as 'conv'

And finally, this to show it visually as a percentage:

select cast(round((cast(sq.Completes as float) /
   cast(sq.Screens as float)),2) * 100 as varchar(25)) 
   + '%' as 'conv'
RThomas
  • 10,702
  • 2
  • 48
  • 61
2

Pure number (it includes round)

cast(100.0*sq.Completes/sq.Screens as decimal(6,0)) conv

With percent

cast(cast(100.0*sq.Completes/sq.Screens as decimal(6,0)) as varchar(6)) + '%' conv
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92