Division question

I'm trying to divide 14/30. I would like to show my
output as 0.4666. Using the % gives me 14. How can I
get the output that I want?
TIA,
Vic
Because your SELECT statement doesn't specifiy any data types the values are
assumed to be integers. Integer division will return an integer result.
You can include a decimal which will give a decimal result:
SELECT 14/30.0
Or you can specify a data type explicitly:
SELECT 14/CAST(30 AS REAL)
Both of the above will return a result with decimal precision but if you
want a particular number of decimals in the output you will have to CAST or
ROUND the result or do the formatting in your client app.
David Portas
SQL Server MVP
|||http://www.aspfaq.com/2483
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Vic" <vduran@.specpro-inc.com> wrote in message
news:13c8101c4440d$fd1cc040$a101280a@.phx.gbl...
> I'm trying to divide 14/30. I would like to show my
> output as 0.4666. Using the % gives me 14. How can I
> get the output that I want?
> TIA,
> Vic

Division question

I'm trying to divide 14/30. I would like to show my
output as 0.4666. Using the % gives me 14. How can I
get the output that I want?
TIA,
VicBecause your SELECT statement doesn't specifiy any data types the values are
assumed to be integers. Integer division will return an integer result.
You can include a decimal which will give a decimal result:
SELECT 14/30.0
Or you can specify a data type explicitly:
SELECT 14/CAST(30 AS REAL)
Both of the above will return a result with decimal precision but if you
want a particular number of decimals in the output you will have to CAST or
ROUND the result or do the formatting in your client app.
David Portas
SQL Server MVP
--|||http://www.aspfaq.com/2483
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Vic" <vduran@.specpro-inc.com> wrote in message
news:13c8101c4440d$fd1cc040$a101280a@.phx
.gbl...
> I'm trying to divide 14/30. I would like to show my
> output as 0.4666. Using the % gives me 14. How can I
> get the output that I want?
> TIA,
> Vic

Division question

I'm trying to divide 14/30. I would like to show my
output as 0.4666. Using the % gives me 14. How can I
get the output that I want?
TIA,
VicBecause your SELECT statement doesn't specifiy any data types the values are
assumed to be integers. Integer division will return an integer result.
You can include a decimal which will give a decimal result:
SELECT 14/30.0
Or you can specify a data type explicitly:
SELECT 14/CAST(30 AS REAL)
Both of the above will return a result with decimal precision but if you
want a particular number of decimals in the output you will have to CAST or
ROUND the result or do the formatting in your client app.
--
David Portas
SQL Server MVP
--|||http://www.aspfaq.com/2483
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Vic" <vduran@.specpro-inc.com> wrote in message
news:13c8101c4440d$fd1cc040$a101280a@.phx.gbl...
> I'm trying to divide 14/30. I would like to show my
> output as 0.4666. Using the % gives me 14. How can I
> get the output that I want?
> TIA,
> Vic

Division not working

The following yields strange results
select [FieldA], [FieldB], ([FieldA]/[FieldB]) as [FieldC] from TableA where
([FieldB]>=1)
sample output is
FieldA FieldB FieldC
4 11 0
3 4 0
3 4 0
5 12 0
5 12 0
4 5 0
Addition, subtraction and multiplication all work properly, but division
isn't. Is there a flag somewhere in SQL that needs to be set for division to
work?
ThanksInteger division yield integer result. try casting one of the columns
involved in the expression to a datatype with greater precedence.
select [FieldA], [FieldB], (([FieldA] * 1.00) / [FieldB]) as [FieldC] from
TableA
where ([FieldB]>=1)
AMB
"mike" wrote:

> The following yields strange results
> select [FieldA], [FieldB], ([FieldA]/[FieldB]) as [FieldC] from TableA where
> ([FieldB]>=1)
> sample output is
> FieldA FieldB FieldC
> 4 11 0
> 3 4 0
> 3 4 0
> 5 12 0
> 5 12 0
> 4 5 0
> Addition, subtraction and multiplication all work properly, but division
> isn't. Is there a flag somewhere in SQL that needs to be set for division
to
> work?
> Thanks|||Hi
Results are fine if the columns are integer datatype. 4/11 = 0 in integer
division
select
FieldA,
FieldB,
(CONVERT(DECIMAL(18,2), FieldA) / CONVERT(DECIMAL(18,2), FieldB)) as
FieldC
from
TableA
where
FieldB>=1
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"mike" <mike@.discussions.microsoft.com> wrote in message
news:95F2FAF7-1282-410B-8E00-831EA32921A6@.microsoft.com...
> The following yields strange results
> select [FieldA], [FieldB], ([FieldA]/[FieldB]) as [FieldC] from TableA
> where
> ([FieldB]>=1)
> sample output is
> FieldA FieldB FieldC
> 4 11 0
> 3 4 0
> 3 4 0
> 5 12 0
> 5 12 0
> 4 5 0
> Addition, subtraction and multiplication all work properly, but division
> isn't. Is there a flag somewhere in SQL that needs to be set for division
> to
> work?
> Thanks|||D'oh!
Thanks, I'd tried casting the entire result, but not each field prior to
division.
"Alejandro Mesa" wrote:
> Integer division yield integer result. try casting one of the columns
> involved in the expression to a datatype with greater precedence.
> select [FieldA], [FieldB], (([FieldA] * 1.00) / [FieldB]) as [FieldC] from
> TableA
> where ([FieldB]>=1)
>
> AMB
> "mike" wrote:
>|||Mike,
If you cast the entire result, then you will be casting the integer result
and this will give you the same problem. It is not the same casting the
variables / columns involved in the expression to yield a higher precedence
datatype as casting the result.
Example:
select cast(1 as numeric(5, 3)) / 2, cast(1 / 2 as numeric(5, 3))
AMB
"mike" wrote:
> D'oh!
> Thanks, I'd tried casting the entire result, but not each field prior to
> division.
>
> "Alejandro Mesa" wrote:
>|||Yes, unfortunately Casting the result is too late.. The Integer division has
already generated the wrong (zero) result...
"mike" wrote:
> D'oh!
> Thanks, I'd tried casting the entire result, but not each field prior to
> division.
>
> "Alejandro Mesa" wrote:
>

divide INT get decimal output

I have written the following query and even though I used CAST if the
number is less then 1 it show up as 0. Here is the query:

select cast(count(SafetyTrainingYN) AS FLOAT)
from ICPCDATA
where SafetyTrainingYN = 1 /
(select count(SafetyTrainingYN)
from ICPCDATA)

Any help would be much appreciated.

Thanks,

jp

remove _nospam_ for my email

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Josh, is this what you're after?

SELECT
(SELECT CAST(COUNT(*) AS FLOAT)
FROM ICPCDATA
WHERE SafetyTrainingYN=1) /
(SELECT COUNT(SafetyTrainingYN)
FROM ICPCDATA)
AS PercentYes

In your query, you're looking for the count of rows where SafetyTrainingYN
is equal to the fraction 1/count(...) (zero when the table has more than 1
row due to integer division).

Hope that helps,
Rich

"Josh Phillips" <phillips3_nospam_@.hotmail.com> wrote in message
news:40914d9e$0$203$75868355@.news.frii.net...
> I have written the following query and even though I used CAST if the
> number is less then 1 it show up as 0. Here is the query:
> select cast(count(SafetyTrainingYN) AS FLOAT)
> from ICPCDATA
> where SafetyTrainingYN = 1 /
> (select count(SafetyTrainingYN)
> from ICPCDATA)
> Any help would be much appreciated.
> Thanks,
> jp
>
> remove _nospam_ for my email
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!