Help with number / percentage format

Question

Help with number / percentage format

jsqldba

SSChampion

Points: 11257
More actions
December 4, 2014 at 2:18 am

#295648

I'm trying to get a calculation based on count(*) to format as a decimal value or percentage.
I keep getting 0s for the solution_rejected_percent column. How can I format this like 0.50 (for 50%)?
thanks
select mi.id, count(*) as cnt,
count(*) + 1 as cntplusone,
cast(count(*) / (count(*) + 1) as numeric(10,2)) as solution_rejected_percent
from metric_instance mi
INNER JOIN incident i
on i.number = mi.id
WHERE mi.definition = 'Solution Rejected'
AND i.state = 'Closed'
group by mi.id
id cnt cntplusone solution_rejected_percent
-------------------------------------------------- ----------- ----------- ---------------------------------------
INC011256 1 2 0.00
INC011290 1 2 0.00
INC011291 1 2 0.00
INC011522 1 2 0.00
INC011799 2 3 0.00

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

IN SQL server if you do a divide an Integer by an integer you get an integer, and a count will only ever produce an integer.

to get round this you need to convert one of the counts to a Decimal and then it will work

Example

Print 100/200

Print 100/CONVERT(DECIMAL(18,2),200)

hope this helps

_________________________________________________________________________
SSC Guide to Posting and Best Practices

jsqldba SSChampion Points: 11257 More actions · Answer 2

many thanks! i was about to go crazy with that. :hehe:

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

Jason-299789 (12/4/2014)
IN SQL server if you do a divide an Integer by an integer you get an integer, and a count will only ever produce an integer.
to get round this you need to convert one of the counts to a Decimal and then it will work
Example
Print 100/200
Print 100/CONVERT(DECIMAL(18,2),200)
hope this helps

Or the quick and dirty solution:

select 100/200.0

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 4

Phil Parkin (12/4/2014)
Jason-299789 (12/4/2014)
IN SQL server if you do a divide an Integer by an integer you get an integer, and a count will only ever produce an integer.
to get round this you need to convert one of the counts to a Decimal and then it will work
Example
Print 100/200
Print 100/CONVERT(DECIMAL(18,2),200)
hope this helps
Or the quick and dirty solution:
select 100/200.0

Aye, that works as well, just thought Id demonstrate it principle converting the INT in line rather than adding a decimal :crazy:

I always wondered why SQL doesn't auto cast the output of a division to a Decimal of max precision.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Jason-299789 (12/4/2014)
Phil Parkin (12/4/2014)
Jason-299789 (12/4/2014)
IN SQL server if you do a divide an Integer by an integer you get an integer, and a count will only ever produce an integer.
to get round this you need to convert one of the counts to a Decimal and then it will work
Example
Print 100/200
Print 100/CONVERT(DECIMAL(18,2),200)
hope this helps
Or the quick and dirty solution:
select 100/200.0
Aye, that works as well, just thought Id demonstrate it principle converting the INT in line rather than adding a decimal :crazy:
I always wondered why SQL doesn't auto cast the output of a division to a Decimal of max precision.

Dunno why they did it that way but I've come to rely on it for different forms of grouping.

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