|
Home > Archive > MS SQL Server > March 2006 > Divide by zero with Sub Query
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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)
| |
| Ben Nevarez 2006-03-27, 1:23 pm |
|
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.
>
|
|
|
|
|