Division Problem in a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Division Problem in a query

Author

Topic

mj
Starting Member

25 Posts

Posted - 2002-07-05 : 10:15:14

Here is the total code for my query (its short) followed by results. What I can't figure out, is why it is not doing the division correctly. I attribute this mostly to my ignorance. I appreciate any help with this.

declare @TotalDeals int

Select @TotalDeals = count(issueNo)
FROM dbo.DEAL
WHERE borrowerstate = 'IL' and moodys is not null

--Print @TotalDeals

SELECT
moodys, count(moodys)as MoodysCount
, (count(moodys)/@TotalDeals) as MoodysPerc,
@TotalDeals
FROM dbo.DEAL
WHERE borrowerstate = 'IL' and moodys is not null
GROUP BY moodys

column headers are shown in the query above

1 0 53
A1/VMIG 1 1 0 53
A1/VMIG-1 1 0 53
Aa1 1 0 53
aa3/VMIG 1 0 53
Aa3/VMIG1 1 0 53
Aaa 5 0 53
Baa 1 0 53
Baa1 1 0 53
Baa2 1 0 53
MIG 1/VMIG 1 2 0 53
NR 36 0 53
Ser C: A1/VMIG1 1 0 53

Kevin Snow
Posting Yak Master

149 Posts

Posted - 2002-07-05 : 10:24:45

@totaldeals is an integer.

You need to be aware that INTEGER division results in an INTEGER result. Fractions are dropped

If you want the result of a division to be 'real', you need to cast the values involved to a type that supports decimals.

ie.
cast(count(moodys) as numeric)/cast(@TotalDeals as Numeric) as MoodysPerc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources