Division Calc in Column Returns 0's or 1's

I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
calculated in a previous grouped view (View1). The column in View2 that
devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
record... but they should be actual numbers instead.
The divide column in View2 looks like this: Number2 / Number 1
The SQL statement looks like this:
SELECT Number1, Number2, Number2 / Number1 AS Expr1
FROM dbo.View1
What do you think is going on? Is SQL Server getting the DataTypes mixed
up?
Thank You!!
Scott Buerkley
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95If your numbers are INTs then you're getting integer divisions. For
example:
SELECT 1/2
returns 0.
Try casting one of them to NUMERIC:
SELECT Number1, Number2, CAST(Number2 AS NUMERIC(10, 5)) / Number1 AS
Expr1
FROM dbo.View1
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95|||Hard to know for sure without DDL and sample data, but the most likely cause
is that Number1 and Number 2 are bot integer types (int or smallint, etc).
Then SQL does an integer divide and always returns an integer result.
Change one of them to a type which can have a decimal part (float, or
decimal, etc) before doing the divide, something like
SELECT Number1, Number2, (Cast Number2 As Float) / Number1 As Expr1
Tom
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95|||Scott Buerkley wrote:
> I am dividing 2 numbers in a view (View2). The 2 numbers we sums of numbe
r
> calculated in a previous grouped view (View1). The column in View2 that
> devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
> record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
>
You're dividing two integers, coming up with a fractional value, and SQL
is rounding it to return it as an integer.|||Yes, the 2 numbers were integers and this worked!!
Thx,
Scott Buerkley
"Mike C#" <xyz@.xyz.com> wrote in message
news:ODvlDeimGHA.464@.TK2MSFTNGP05.phx.gbl...
> If your numbers are INTs then you're getting integer divisions. For
> example:
> SELECT 1/2
> returns 0.
> Try casting one of them to NUMERIC:
> SELECT Number1, Number2, CAST(Number2 AS NUMERIC(10, 5)) / Number1 AS
> Expr1
> FROM dbo.View1
> "Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
> news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95|||Yes, you were all correct. The 2 numbers were integers.
It is working now. Thanks for your help!!
Thx,
Scott Buerkley
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95