Divide by zero

In the following query I sometimes have DIVIDE BY ZERO issues. How can I
put the CASE (SOME VARIABLE) WHEN 0 THEN 0 ELSE statement into the query so
that I don't get the error.
SUM(MedExp) is sometimes = 0
SUM(TotalMonths) is sometimes = 0
SUM(TotalMem) is sometimes = 0
I'm not sure how to set percentof, pmpm, and permem to zero if the "divide
by" (one of the three above) is zero?
Here is the query. Any help would be great.
SELECT MCO, SUM([Pharmacy Exp]) AS phexp, (SUM([Pharmacy Exp]) /
SUM(MedExp)) as percentof, (SUM([Pharmacy Exp]) / SUM(TotalMonths)) AS pmpm,
(SUM([Pharmacy Exp]) / SUM(TotalMem)) AS permem from sheet1$ where medexp <>
'' and totalmem <> '' and period = '3Q04' AND (domicile LIKE '%ct%' OR
domicile LIKE '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR
domicile LIKE '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR
domicile LIKE '%nj%') GROUP BY MCO order by permem descTry,
SELECT
MCO,
SUM([Pharmacy Exp]) AS phexp,
isnull((SUM([Pharmacy Exp]) / nullif(SUM(MedExp), 0)), 0) as percentof,
isnull((SUM([Pharmacy Exp]) / nullif(SUM(TotalMonths), 0)), 0) AS pmpm,
isnull((SUM([Pharmacy Exp]) / nullif(SUM(TotalMem), 0)), 0) AS permem
from
sheet1$ where medexp <> ''
and totalmem <> ''
and period = '3Q04'
AND (
domicile LIKE '%ct%'
OR domicile LIKE '%ma%'
OR domicile LIKE '%nh%'
OR domicile LIKE '%me%'
OR domicile LIKE '%ri%'
OR domicile LIKE '%vt%'
OR domicile LIKE '%ny%'
OR domicile LIKE '%nj%'
)
GROUP BY
MCO
order by
permem desc;
AMB
"William" wrote:

> In the following query I sometimes have DIVIDE BY ZERO issues. How can I
> put the CASE (SOME VARIABLE) WHEN 0 THEN 0 ELSE statement into the query s
o
> that I don't get the error.
> SUM(MedExp) is sometimes = 0
> SUM(TotalMonths) is sometimes = 0
> SUM(TotalMem) is sometimes = 0
> I'm not sure how to set percentof, pmpm, and permem to zero if the "divide
> by" (one of the three above) is zero?
> Here is the query. Any help would be great.
> SELECT MCO, SUM([Pharmacy Exp]) AS phexp, (SUM([Pharmacy Exp]) /
> SUM(MedExp)) as percentof, (SUM([Pharmacy Exp]) / SUM(TotalMonths)) AS pmp
m,
> (SUM([Pharmacy Exp]) / SUM(TotalMem)) AS permem from sheet1$ where medexp
<>
> '' and totalmem <> '' and period = '3Q04' AND (domicile LIKE '%ct%' OR
> domicile LIKE '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR
> domicile LIKE '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR
> domicile LIKE '%nj%') GROUP BY MCO order by permem desc
>
>|||Try this
SELECT MCO, SUM([Pharmacy Exp]) AS phexp,
(CASE WHEN SUM(MedExp) > 0 THEN SUM([Pharmacy Exp]) /SUM(MedExp) ELSE
SUM([Pharmacy Exp]) END ) as percentof,
(CASE WHEN SUM(TotalMonths) > 0 THEN SUM([Pharmacy Exp]) / SUM(TotalMonths)
ELSE SUM([Pharmacy Exp]) END ) AS pmpm,
(CASE WHEN SUM(TotalMem) > 0 THEN SUM([Pharmacy Exp]) / SUM(TotalMem) ELSE
SUM([Pharmacy Exp]) END ) AS permem,
from sheet1$ where medexp <>
'' and totalmem <> '' and period = '3Q04' AND (domicile LIKE '%ct%' OR
domicile LIKE '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR
domicile LIKE '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR
domicile LIKE '%nj%') GROUP BY MCO order by permem desc
Thank you
Baiju
"William" <da@.northernit.net> wrote in message
news:VPb4e.1793$uw2.417@.twister.nyroc.rr.com...
> In the following query I sometimes have DIVIDE BY ZERO issues. How can I
> put the CASE (SOME VARIABLE) WHEN 0 THEN 0 ELSE statement into the query
so
> that I don't get the error.
> SUM(MedExp) is sometimes = 0
> SUM(TotalMonths) is sometimes = 0
> SUM(TotalMem) is sometimes = 0
> I'm not sure how to set percentof, pmpm, and permem to zero if the "divide
> by" (one of the three above) is zero?
> Here is the query. Any help would be great.
> SELECT MCO, SUM([Pharmacy Exp]) AS phexp, (SUM([Pharmacy Exp]) /
> SUM(MedExp)) as percentof, (SUM([Pharmacy Exp]) / SUM(TotalMonths)) AS
pmpm,
> (SUM([Pharmacy Exp]) / SUM(TotalMem)) AS permem from sheet1$ where medexp
<>
> '' and totalmem <> '' and period = '3Q04' AND (domicile LIKE '%ct%' OR
> domicile LIKE '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR
> domicile LIKE '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR
> domicile LIKE '%nj%') GROUP BY MCO order by permem desc
>