Division

Newbie Question.
While using a query/subquery to get a percentage I keep getting zero. I
assume this is becuase the number returned is less than 1. How can I make
this work right?
Example
Select 123/12345
returns 0 when it should equal 0.00996
I have tried converting using decimal(5,5) but either this is wrong, or I am
doing it wrong.
Thanks
DaveThe result of an integer division will be integer.
Example:
select 4 / 2, 4.00 / 2, 4 / 2.00, cast(2 as numeric(5, 2)) / 4
AMB
"Dave S." wrote:

> Newbie Question.
> While using a query/subquery to get a percentage I keep getting zero. I
> assume this is becuase the number returned is less than 1. How can I make
> this work right?
> Example
> Select 123/12345
> returns 0 when it should equal 0.00996
> I have tried converting using decimal(5,5) but either this is wrong, or I
am
> doing it wrong.
> Thanks
> Dave
>
>|||Select 123/(12345 * 1.0)
Keith
"Dave S." <davidstedman@.colliergov.net> wrote in message
news:uUS4OO5EFHA.1932@.TK2MSFTNGP14.phx.gbl...
> Newbie Question.
> While using a query/subquery to get a percentage I keep getting zero. I
> assume this is becuase the number returned is less than 1. How can I make
> this work right?
> Example
> Select 123/12345
> returns 0 when it should equal 0.00996
> I have tried converting using decimal(5,5) but either this is wrong, or I
am
> doing it wrong.
> Thanks
> Dave
>

Divide by zero with Sub Query

Hello,
I need to dived a number by a summed value from a subquery. Any
suggestions on how to trap for zeros would be appreciated.
Thanks in advance,
sck10
SELECT
tsp01.StoreRevenue /
SELECT SUM(tft02.TotalFunded)
FROM
MyTable Tsp02
WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
GROUP BY Tsp02.Track_ID)
What do you want to do if you have found a 0?
Try
select 20 / nullif(2, 0) -- returns 10
select 20 / nullif(0, 0) -- returns NULL
Ben Nevarez, MCDBA, OCP
Database Administrator
"sck10" wrote:

> Hello,
> I need to dived a number by a summed value from a subquery. Any
> suggestions on how to trap for zeros would be appreciated.
> --
> Thanks in advance,
> sck10
> SELECT
> tsp01.StoreRevenue /
> SELECT SUM(tft02.TotalFunded)
> FROM
> MyTable Tsp02
> WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID)
>
>
|||SELECT
CASE
WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
END
FROM tsp01, tsp02
where (tsp02.Track_ID = tsp01.Track_ID)
GROUP BY Tsp02.Track_ID
let me know if this works for you.
|||Thanks Zomer,
This worked...
"zomer" <noneee@.gmail.com> wrote in message
news:1143485957.082088.145680@.i39g2000cwa.googlegr oups.com...
> SELECT
> CASE
> WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
> SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
> END
> FROM tsp01, tsp02
> where (tsp02.Track_ID = tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID
> let me know if this works for you.
>

Divide by zero with Sub Query

Hello,
I need to dived a number by a summed value from a subquery. Any
suggestions on how to trap for zeros would be appreciated.
--
Thanks in advance,
sck10
SELECT
tsp01.StoreRevenue /
SELECT SUM(tft02.TotalFunded)
FROM
MyTable Tsp02
WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
GROUP BY Tsp02.Track_ID)What do you want to do if you have found a 0?
Try
select 20 / nullif(2, 0) -- returns 10
select 20 / nullif(0, 0) -- returns NULL
Ben Nevarez, MCDBA, OCP
Database Administrator
"sck10" wrote:
> Hello,
> I need to dived a number by a summed value from a subquery. Any
> suggestions on how to trap for zeros would be appreciated.
> --
> Thanks in advance,
> sck10
> SELECT
> tsp01.StoreRevenue /
> SELECT SUM(tft02.TotalFunded)
> FROM
> MyTable Tsp02
> WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID)
>
>|||SELECT
CASE
WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
END
FROM tsp01, tsp02
where (tsp02.Track_ID = tsp01.Track_ID)
GROUP BY Tsp02.Track_ID
let me know if this works for you.|||Thanks Zomer,
This worked...
"zomer" <noneee@.gmail.com> wrote in message
news:1143485957.082088.145680@.i39g2000cwa.googlegroups.com...
> SELECT
> CASE
> WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
> SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
> END
> FROM tsp01, tsp02
> where (tsp02.Track_ID = tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID
> let me know if this works for you.
>

Divide by zero with Sub Query

Hello,
I need to dived a number by a summed value from a subquery. Any
suggestions on how to trap for zeros would be appreciated.
--
Thanks in advance,
sck10
SELECT
tsp01.StoreRevenue /
SELECT SUM(tft02.TotalFunded)
FROM
MyTable Tsp02
WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
GROUP BY Tsp02.Track_ID)What do you want to do if you have found a 0?
Try
select 20 / nullif(2, 0) -- returns 10
select 20 / nullif(0, 0) -- returns NULL
Ben Nevarez, MCDBA, OCP
Database Administrator
"sck10" wrote:

> Hello,
> I need to dived a number by a summed value from a subquery. Any
> suggestions on how to trap for zeros would be appreciated.
> --
> Thanks in advance,
> sck10
> SELECT
> tsp01.StoreRevenue /
> SELECT SUM(tft02.TotalFunded)
> FROM
> MyTable Tsp02
> WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID)
>
>|||SELECT
CASE
WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
END
FROM tsp01, tsp02
where (tsp02.Track_ID = tsp01.Track_ID)
GROUP BY Tsp02.Track_ID
let me know if this works for you.|||Thanks Zomer,
This worked...
"zomer" <noneee@.gmail.com> wrote in message
news:1143485957.082088.145680@.i39g2000cwa.googlegroups.com...
> SELECT
> CASE
> WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
> SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
> END
> FROM tsp01, tsp02
> where (tsp02.Track_ID = tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID
> let me know if this works for you.
>