Divide by Zero Encountered with PARTITION BY

Question

I have multiple subqueries for groups of students (a,b,c) and I want to display the results by subject. Some groups may not be in certain subjects.

I'm receiving a 'divide by zero' error when performing a calculation. How can I modify the code below to display 'null' when the result is n/a?

STR(((100.0*COUNT(a.STC_grade))/SUM(COUNT(a.STC_grade))OVER(partition by dbo.S85_STUDENT_ACAD_CRED.stc_subject)), 5,0)  +'%' AS 'PERCENT'

Any assistance would be appreciated.

Does this answer your question? [How to avoid the "divide by zero" error in SQL?](https://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql) — Thom A, Jun 16 '23 at 16:22
If the denominator is occasionally yielding 0, then you could just check for that condition and have this output something else instead: `CASE WHEN SUM(COUNT(a.STC_grade))OVER(partition by dbo.S85_STUDENT_ACAD_CRED.stc_subject)) = 0 THEN '0%' ELSE STR(((100.0*COUNT(a.STC_grade))/SUM(COUNT(a.STC_grade))OVER(partition by dbo.S85_STUDENT_ACAD_CRED.stc_subject)), 5,0) +'%' END as 'Percent'` — JNevill, Jun 16 '23 at 16:23
@JNevill - I just tried your suggestion (with the exception of a space added between isnull) and received this "Msg 130, Level 15, State 1, Line 34 Cannot perform an aggregate function on an expression containing an aggregate or a subquery. — KLR, Jun 16 '23 at 16:33
@ThomA - I am having trouble getting it to work since I am using a 'partition by...'. Unsure where/how to word/place it... — KLR, Jun 16 '23 at 16:35
@ThomA - I'm so sorry - I just can't get it to work - would you be able to tell me where to insert the nullif etc to get it to work? STR(((100.0*COUNT(a.STC_grade))/SUM(COUNT(a.STC_grade))OVER(partition by dbo.S85_STUDENT_ACAD_CRED.stc_subject)), 5,0) +'%' AS 'PERCENT' — KLR, Jun 16 '23 at 16:59
@ThomA I was wondering if you could 'plug in' the nullif for me - I'm not that brilliant at sql...sorry to be a bother. — KLR, Jun 16 '23 at 17:44
STR( case when COUNT(a.STC_grade)<>0 then 100.0*SUM(COUNT(a.STC_grade))OVER(partition by dbo.S85_STUDENT_ACAD_CRED.stc_subject) /COUNT(a.STC_grade) else 0.0 end , 5,0) +'%' AS 'PERCENT' — ValNik, Jun 16 '23 at 21:28
@ValNik - I went ahead and tried that, but it gave me some really high percentages - the problem seems to be with the OVER(partition by..) part. — KLR, Jun 16 '23 at 22:04

score 0 · Accepted Answer · answered Jun 19 '23 at 12:44

If you added input data, your query and what your expectation were, it would be easies to recreate your situation. Not sure that my example fully covers your input data but I could get your error and fix it by adding isnull in SUM(COUNT(isnull(sg.STC_grade,0)))

create table Subjects (Sbj_Id int)
create table Groups (Grp_Id int)
create table Sub_Grp (Grp_id int, Sbj_Id int, STC int, STC_grade int)

insert into Subjects
values
(1),
(2),
(3)

insert into Groups
values
(1),
(2)

insert into Sub_Grp
values
(1,1,1,5),
(1,2,1,15),
(1,2,2,30),
(1,2,3,10),
(2,2,1,20),
(2,3,1,40)

select g.Grp_Id as [Group], s.Sbj_Id as [Subject], sg.STC as [Student], sg.STC_grade
from Groups g
cross apply Subjects s
left join Sub_Grp sg on sg.Grp_Id = g.Grp_id and sg.Sbj_Id = s.Sbj_Id

Highlighted rows from the screen above will ruin the query below without isnull.

select g.Grp_Id as [Group], s.Sbj_Id as [Subject],
STR(((100.0*COUNT(sg.STC_grade))/SUM(COUNT(isnull(sg.STC_grade,0)))OVER(partition by sg.Sbj_Id)), 5,0)  +'%' AS 'PERCENT'
from Groups g
cross apply Subjects s
left join Sub_Grp sg on sg.Grp_Id = g.Grp_id and sg.Sbj_Id = s.Sbj_Id
group by g.Grp_Id,s.Sbj_Id,sg.Sbj_Id
order by s.Sbj_Id, g.Grp_Id

Divide by Zero Encountered with PARTITION BY

1 Answers1