Answered by:
Divide by 0  having tunnel vision

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
Question
Answers

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
 Marked as answer by Allen Li  MSFTModerator Friday, July 12, 2013 6:48 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 70461 Bootcamp: Querying Microsoft SQL Server 2012 
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
 Marked as answer by Allen Li  MSFTModerator Friday, July 12, 2013 6:48 AM