# Divide by 0 - having tunnel vision

### Question

• I have attempted this query many times using isnul(expresssion, 0) / nullif(expression,0)

the problem appears when i try to do a partion by sales person....

can anyone assist?

this works

```select  SUM(NUMERATOR)NUMERATOR,
DENOMINATOR,
SALES_NBR,
SALES_NAME,
LOCATION_DESC,
100*((SUM(NUMERATOR)) / (SUM(SUM(NUMERATOR))  OVER())) AS PCT
from #TEMP
GROUP BY SALES_NBR,	NUMERATOR ,SALES_NAME , DENOMINATOR, LOCATION_DESC
ORDER BY SALES_NAME```

This does not

```select  SUM(NUMERATOR)NUMERATOR,
DENOMINATOR,
SALES_NBR,
SALES_NAME,
LOCATION_DESC,
100*((SUM(NUMERATOR)) / (SUM(SUM(NUMERATOR))  OVER(PARTITION BY SALES_NBR ))) AS PCT
from #TEMP
GROUP BY SALES_NBR,	NUMERATOR ,SALES_NAME , DENOMINATOR, LOCATION_DESC
ORDER BY SALES_NAME```

I am trying to get a percentage of by numerator in order to allocate the percentage to the denominator

Right now, the total comes up right (100) for percentage, but the percentage needs to be allocated for the sales person and each location that they service

KDW

Tuesday, July 02, 2013 6:45 PM

• Try

```select  SUM(NUMERATOR)NUMERATOR,
DENOMINATOR,
SALES_NBR,
SALES_NAME,
LOCATION_DESC,
100*((SUM(NUMERATOR)) / isnull(SUM(SUM(NUMERATOR))  OVER(PARTITION BY SALES_NBR )),1) AS PCT
from #TEMP
GROUP BY SALES_NBR,	NUMERATOR ,SALES_NAME , DENOMINATOR, LOCATION_DESC
ORDER BY SALES_NAME```

Many Thanks & Best Regards, Hua Min

Wednesday, July 03, 2013 3:02 AM