none
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

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

    Wednesday, July 03, 2013 3:02 AM

All replies