SQL Server group by count eliminate duplicates

Question

My data is like below:

ClassId ClassName   StudentId   Subject     SubjectId  
-----------------------------------------------------
1         ESL        12         English      20  
1         ESL        13         Science      30  
1         ESL        12         Social       40  
1         ESL        12         Maths        50

Required output: parameters are Subject column values

ClassId ClassName   TotalStudents   SubjectIds   
-----------------------------------------------    
1        ESL         2               20, 40, 50, 30

When one student takes multiple subjects then count student only once, so in the above data 12 is one student id takes multiple subjects so counted only once. TotalStudents value is 2 (1 from student id 12 and 1 from student id 13)

I wrote this query:

Declare @subjectids string  
set @subjectids = '20,30,40,50'  

-- will split @subjectids  and store in temp table     
select 
    classname, classid, Count(Distinct StudentId)  
from 
    mytable  
where 
    SubjectsIds in @subjectIds  
group by 
    ClassId, ClassName, SubjectId,

but I get this output:

ClassId ClassName   TotalStudents   SubjectIds  
----------------------------------------------    
1        ESL         1               20  
1        ESL         1               30  
1        ESL         1               40  
1        ESL         1               50

Any help in eliminating duplicates?
Edited: My basic question is how to count student only once when the same students takes multiple subjects from same class? While counting eliminate duplicates.

`in` doesn't work with string literals. The proper syntax is `where SubjectsIds in (20,30,40,50)`. But why do you need it anyway? — torvin, Oct 01 '15 at 02:50
I need to count student only once when student takes multiple subjects from same class? — Blue Cloud, Oct 01 '15 at 12:43

SQL Server group by count eliminate duplicates

0 Answers0