Re: simple math question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 09/02/04 

Date: Thu, 2 Sep 2004 08:37:44 -0700

Chris

The issue is that SQL Server thinks it is dividing two integers. When both
operands are integers, then integer division is performed, then any
remainder is discarded.
You have to force one of the operands to be NOT an integer, any type that
can carry decimal digits might work for you.

For a integer constant, you can change it to non-integer by just adding a
decimal point.

So SQL Server will see this:

count(*)/12. as dividing an integer by a decimal and you will get decimal
digits. Maybe more than you want. :-)

If you want a specific number of digits, you will have to use convert to a
type that has that number of digits. Please read about the decimal and
numeric types in the Books Online

use pubs
select convert(numeric(10,2), count(*)/12.)
from titles 

-- 
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ChrisR" <anonymous@discussions.microsoft.com> wrote in message
news:0c2201c49100$afe192c0$a401280a@phx.gbl...
> Thanks guys for the speedy response. If Im doing a count
> what do I do?
>
> select LastYear=
> count(*)/ 12
> from bla bla
>
> Do I need to set the count to a parameter. I tried it and
> its not working too well.
>
>
> >-----Original Message-----
> >explicitly use the period ( . ) after the 1 for a type
> conversion, i.e.,
> >select 1./3
> >
> >hth
> >Eric
> >
> >
> >
> >ChrisR wrote:
> >> sql2k sp3
> >>
> >> select (1 / 3)
> >>
> >> gives me the result of 0.
> >>
> >> I dont need precise results, but would at least like to
> >> see .33. How can this be accomplished?
> >>
> >> TIA, ChrisR
> >
> >
> >.
> >

Relevant Pages

  • Re: Mergefield formatting codes seem to combine digits in numeric data
    ... the problem /may/ be to do with the money type. ... Something worth trying is to use a cast or convert function in a SQL Server ... If no formatting fields are supplied in the MergeField, ... Instead Word seems to combine digits. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Complex Query
    ... Everything works except chomping the year to the last 2 digits. ... possible use standard and well-defined formats like the ISO formats. ... State what version of SQL Server you are using and specify the content ...
    (comp.databases.ms-sqlserver)
  • Re: Multiplying numeric(19,4) Values
    ... The reason for this is how SQL Server handles precision for numeric ... The ultimate goal is not to lose any precision. ... and the first has 5 digits before the decimal place and the ... is where the setting of NUMERIC_ROUNDABORT and ARITHABORT come in. ...
    (microsoft.public.sqlserver.programming)
  • Re: What are the strings that define a connections server version?
    ... The string is of the form major.minor.build, where major and minor are exactly two digits and build is exactly four digits. ... What should I get back as string if I was connected to sql server 2000 ... Does the 2005 versions support ALL 2000 SQL syntax or do I need to detect the version to generate the dynamic sql statements in my vb code to adapt to the correct version of sql server. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: The Many Flavors of SQL - Can a SQL Server query work in MS Access?
    ... Maybe because of failure to include the additional parentheses at one time ... In SQL Server, by default, if one of the operands is ... in the Query Analyzer on a Windows 2000 Server box running SQL Server ...
    (microsoft.public.access.queries)