Need help with ratio calculation...

WebAppDeveloper
WebAppDeveloper used Ask the Experts™
on
Hi,

In SQL Server, how do I calculate the ration of the following 2 numbers? In other words, I'd like to know what is the ration of Count2 to Count1. Please provide the SQL.

Count1 = 125294
Count2 = 241357

Thanks.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2008
Awarded 2008
Commented:
select (count2field/cast(count1field as decimal(18,2))) * 100
from tablename
AneeshDatabase Consultant
Top Expert 2009
Commented:
SELECT Count1*100.0 / Count2
Kevin CrossChief Technology Officer
Most Valuable Expert 2011
Commented:
The above techniques are correct for allowing you to get decimal response.  Otherwise, SQL will treat this as integer division and drop the fractional portion of response.

However, I think for ratio, logic should be this:
SELECT Count2 *1.0 / Count1

Open in new window

11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Awarded 2008
Awarded 2008
Commented:
Im not sure Count1*100.0 / Count2  is correct.

In mine, I was just moving the decimal spot 2 ways...
Chief Technology Officer
Most Valuable Expert 2011
Commented:
Maybe I am thinking incorrectly, but I think of ratio like this:
DECLARE @count1 INT
DECLARE @count2 INT
 
SET @count1 = 125294;
SET @count2 = 241357;
 
SELECT CONVERT(VARCHAR, CAST(@count2 * 1.0 / @count1 AS MONEY)) + ':1';

Open in new window

Awarded 2008
Awarded 2008
Commented:
you're right...I had my 1 and 2 switched by accident...
Awarded 2008
Awarded 2008
Commented:
whoops...no i didn't...i was looking at the post by aneesh.  :)
Kevin CrossChief Technology Officer
Most Valuable Expert 2011
Commented:
Yeah, your post had it correct order.  Aneesh had it reversed, but with shortcut.
I was just reading ratio differently, so made suggestion on 1.0 instead of 100.0.

Author

Commented:
So far, i like mwvisa1 solution where he returns # : #, which comes out to be " 1.93:1 ".  How do I round the 1.93 up to 2?

DECLARE @count1 INT
DECLARE @count2 INT
 
SET @count1 = 125294;
SET @count2 = 241357;
 
SELECT CONVERT(VARCHAR, CAST(@count2 * 1.0 / @count1 AS MONEY)) + ':1';
Awarded 2008
Awarded 2008
Commented:
SELECT CONVERT(VARCHAR, cast(CAST(@count2 * 1.0 / @count1 AS MONEY)as int)) + ':1';
Kevin CrossChief Technology Officer
Most Valuable Expert 2011
Commented:
Or like this since I only used MONEY to use its number of decimal places.
SELECT CONVERT(VARCHAR, CAST(@count2 * 1.0 / @count1 AS DECIMAL(10,0))) + ':1';

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial