how to use NULLIF with aggregate function

I’m using SQL Management Studio 2012. I’m getting a ‘Divide by zero error encountered’ error. I’m aware this requires the use of NULLIF but I’m unsure how to use it in my query.

This is my query

select 
    Ward,
    LocalAuthority,
    Fiscal,
    DR,

    cast(DR*100.0 /SUM(DR) OVER (
        partition by localauthority, fiscal
        order by fiscal asc) as Decimal (10,2)) as [DR%]
from (
    select  
        Fiscal,
        LocalAuthority,
        Ward,
        sum(case when code = 'DR' then 1 else 0 end) as DR
    from [dbo].[Table]
    where datetimeofcall >= '2014-04-01' 
    group by
        ward,
        localauthority,f
        fiscal
) as A

I’ve actually asked this question before which some kind person answered excellently but that was without the OVER() and SUM aggregate function, I’m getting in a muddle with the syntax. I’ve been trying various connotations of the below without success.

cast(DR*100.0 /NULLIF(SUM(DR,0),1) OVER(partition by localauthority,fiscal order by fiscal asc) as Decimal (10,2)) as [DR%]

Answer

Use Case Expression to identify the 0 in prior

SELECT Ward, LocalAuthority, Fiscal, DR,
CASE WHEN SUM(DR) OVER(PARTITION BY localauthority, fiscal ORDER BY fiscal asc) = 0 THEN 0 ELSE CAST(DR*100.0 / SUM(DR) OVER(PARTITION BY  localauthority,fiscal ORDER BY fiscal) AS Decimal (10,2)) END AS [DR%]
FROM (
      SELECT Fiscal, LocalAuthority, Ward, SUM(CASE WHEN code = 'DR' THEN 1 ELSE 0 END) AS DR
      FROM [dbo].[Table]
      WHERE datetimeofcall >='2014-04-01' 
      GROUP BY ward, localauthority, fiscal
     ) AS A