Count of Task based on Students and count of task by students by Date-CodePudding

I achieved the count of each tasks performed by Students from the below Query. In addition I also need to get the count based on tasks performed by students by date on the same query itself. How can I achieve it?

SELECT  DISTINCT Date
             , Task_Name[Task Performed]
             , Student_Name [Name]
             , COUNT(Name)[Count]
    FROM Datamining.dbo.Registry 

GROUP BY Date, Task_Name, Student_Name

The result for above query is

Date          Task Performed     Name           Count
10/1/2021     A                  Joseph         2
10/1/2021     B                  Sherin         3
10/2/2021     A                  Joseph         1
10/2/2021     B                  Sherin         2
10/3/2021     A                  Joseph         3
10/3/2021     B                  Sherin         3

Expected Results

Date          Task Performed     Name           Count     Total Count by Date
10/1/2021     A                  Joseph         2         5
10/1/2021     B                  Sherin         3         5
10/2/2021     A                  Joseph         1         3
10/2/2021     B                  Sherin         2         3
10/3/2021     A                  Joseph         3         6
10/3/2021     B                  Sherin         3         6

How this expected results can be achieved from the above query.

CodePudding user response：

I did it using an inner query: Also Distinct is not needed so I deleted it:

SELECT   r.Date
         , r.Task_Name[Task Performed]
         , r.Student_Name [Name]
         , COUNT(r.Name)[Count]
         ,(
         select count(*) from  Datamining.dbo.Registry rr
         where rr.Date = r.Date
         group by date 
         )  TotalCountByDate         
FROM Datamining.dbo.Registry r
GROUP BY r.Date, r.Task_Name, r.Student_Name

CodePudding user response：

You can use a windowed sum

SELECT   r.Date
         , r.Task_Name[Task Performed]
         , r.Student_Name [Name]
         , COUNT(*) [Count]
         , SUM(COUNT(*)) OVER (PARTITION BY r.Date) TotalCountByDate         
FROM Datamining.dbo.Registry r
GROUP BY r.Date, r.Task_Name, r.Student_Name