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

All replies

• Related percent on base calculation:

http://www.sqlusa.com/bestpractices/percentonbase/

Can you introduce a CTE or two to simplify logic?

It would be helpful if you provide testable script.

Kalman Toth Database & OLAP Architect sqlusa.com
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Wednesday, July 03, 2013 1:03 AM
• 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