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.

Answer 1

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