Help with % calculation please

Question

Help with % calculation please

faulknerwilliam2

SSCrazy

Points: 2299
More actions
March 6, 2017 at 7:29 am

#341450

Hello
I am trying to get an output that will show me:
Total number of deliveries missed / total deliveries expressed as a %
So I have scripted this as:
SUM(a.fieldmissed) / SUM(a.fieldtotal) *100 AS [Deliveries Missed]
But I am getting a return of 0 (zero) when the values are say 200 / 14000, when I'm hoping it should say 1.4.
Please can someone help point out what I'm doing wrong? I feel I'm missing a function somewhere.
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

faulknerwilliam2 - Monday, March 6, 2017 7:29 AM
Hello
I am trying to get an output that will show me:
Total number of deliveries missed / total deliveries expressed as a %
So I have scripted this as:
SUM(a.fieldmissed) / SUM(a.fieldtotal) *100 AS [Deliveries Missed]
But I am getting a return of 0 (zero) when the values are say 200 / 14000, when I'm hoping it should say 1.4.
Please can someone help point out what I'm doing wrong? I feel I'm missing a function somewhere.
Thanks.

Use CAST( SUM(a.fieldmissed) AS decimal(18,8)) / SUM(a.fieldtotal) *100 AS [Deliveries Missed]
You're being victim of something called "integer division". This is an expected functionality but can take you by surprise if you don't pay attention or know how it works.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

faulknerwilliam2 SSCrazy Points: 2299 More actions · Answer 2

Thanks for the prompt and accurate response. Much appreciated.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

Luis is spot on about how this occurred. As a simplification of what he posted, unless the SUM of a.fieldmissed will create a number larger than 13 significant digits, you can do this without explicit casting. Move the "100" and change it to have a decimal place.
SUM(a.fieldmissed)*100.0 / SUM(a.fieldtotal) AS [Deliveries Missed]

--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)

faulknerwilliam2 SSCrazy Points: 2299 More actions · Answer 4

Jeff Moden - Monday, March 6, 2017 8:28 AM
Luis is spot on about how this occurred. As a simplification of what he posted, unless the SUM of a.fieldmissed will create a number larger than 13 significant digits, you can do this without explicit casting. Move the "100" and change it to have a decimal place.
SUM(a.fieldmissed)*100.0 / SUM(a.fieldtotal) AS [Deliveries Missed]

Thanks for taking the trouble to reply Jeff. Your solution works well as well.

Just to push my luck a bit, but do you know if there's a way of restricting the output to two decimal places? I know I can tidy up decimal places in reporting services, but would be handy to do in the SQL as well.

Thanks.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Cast as something like decimal(7,2). Don't take my word for that, though - make sure you read and understand the documentation about the decimal data type.

John

andrew gothard SSChampion Points: 12301 More actions · Answer 6

CAST(<Calculation> AS NUMERIC(6, 2))

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

faulknerwilliam2 SSCrazy Points: 2299 More actions · Answer 7

andrew gothard - Tuesday, March 7, 2017 2:50 AM
CAST(<Calculation> AS NUMERIC(6, 2))

That did the trick:

CAST(SUM(a.fieldmissed)*100.0 / SUM(a.fieldtotal) AS numeric (10,2))

Many thanks