how to use NULLIF with aggregate function

Question

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%]

Vignesh Kumar A · Accepted Answer · 2019-11-04 12:04:35Z

1

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

answered Nov 4, 2019 at 12:04

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to use NULLIF with aggregate function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related