Count(distinct) over (partition by) sql server 2016

Question

I have a table like the below

class_no	student_id	subject_name
1	636	Physics
1	636	Chemistry
1	637	Maths
1	638	German
1	639	Physics

Create table #T_Test(
     class_no int
     ,student_id int
     ,subject_name nvarchar(25)
);
Insert into #T_Test ([class_no],[student_id],[subject_name]) values
(1,636,'Physics'),
(1,636,'Chemistry'),
(1,637,'Maths'),
(1,638,'German'),
(1,639,'Physics')

I want to get the count of distinct student_id, also count grouped_by subject_names

class_no	subject_name	total_subjects	total_students
1	Physics	2	4
1	Chemistry	1	4
1	Maths	1	4
1	German	1	4
1	Physics	1	4

Below is what I have done so far, how can I get the distinct count(student_id) included in the resultset

SELECT  DISTINCT t.class_no
        ,t.subject_name
        ,count(*) total_subject
FROM        #T_Test t
WHERE       t.class_no = 1
GROUP BY    t.class_no,subject_name

Answer 1

I think You are looking for this query:

SELECT DISTINCT 
    t.class_no,
    t.subject_name,
    total_subjects = Count(t.subject_name) OVER(Partition By t.class_no, t.subject_name),
    total_students = (SELECT COUNT(Distinct [student_id]) FROM #T_Test)
FROM    #T_Test t
WHERE   t.class_no = 1