Cumulative sum and cumulative count in sql

I am trying establish treatment courses for patients, below is sample table I am working on.

PatientID	SessionDate	Next_date	diff	courses	Coursenum	course_Count	Total Sesion
10000	12/13/2012	NULL	1.1	start	1	1	10
10000	12/14/2012	12/13/2012	1	existing	1	2	10
10000	12/14/2012	12/14/2012	0	existing	1	3	10
10000	12/17/2012	12/14/2012	3	existing	1	4	10
10000	12/18/2012	12/17/2012	1	existing	1	5	10
10000	12/19/2012	12/18/2012	1	existing	1	6	10
10000	12/21/2012	12/19/2012	2	existing	1	7	10
10000	12/21/2012	12/21/2012	0	existing	1	8	10
10000	12/22/2012	12/21/2012	1	existing	1	9	10
10000	12/24/2012	12/22/2012	2	existing	1	10	10
10000	9/17/2015	1/25/2013	965	start	2	1	20
10000	9/18/2015	9/17/2015	1	existing	2	2	20
10000	9/21/2015	9/18/2015	3	existing	2	3	20
10000	9/22/2015	9/21/2015	1	existing	2	4	20
10000	9/23/2015	9/22/2015	1	existing	2	5	20
10000	9/25/2015	9/23/2015	2	existing	2	6	20
10000	9/28/2015	9/25/2015	3	existing	2	7	20
10000	9/29/2015	9/28/2015	1	existing	2	8	20
10000	9/30/2015	9/29/2015	1	existing	2	9	20
10000	10/2/2015	9/30/2015	2	existing	2	10	20
10000	10/5/2015	10/2/2015	3	existing	2	11	20
10000	10/6/2015	10/5/2015	1	existing	2	12	20
10000	10/7/2015	10/6/2015	1	existing	2	13	20
10000	10/9/2015	10/7/2015	2	existing	2	14	20
10000	10/12/2015	10/9/2015	3	existing	2	15	20
10000	10/13/2015	10/12/2015	1	existing	2	16	20
10000	10/14/2015	10/13/2015	1	existing	2	17	20
10000	10/16/2015	10/14/2015	2	existing	2	18	20
10000	10/19/2015	10/16/2015	3	existing	2	19	20
10000	10/20/2015	10/19/2015	1	existing	2	20	20

I achieved this output using python

Note: 1.1 in diff column is just a placeholeder for NULL

sessions['Coursenum']=(sessions.status.eq('start')).cumsum() sessions['course_count']=sessions.groupby(['Coursenum']).cumcount()+1 sessions['TotalSessions']=sessions.groupby('Coursenum')['course_count'].transform('max')

I wanted to program this using sql. Is there a way to do this in sql?

Solution

We can use window functions in SQL whenever we want to show group-level aggregations in raw data itself.

select *, max(course_Count) over (partition by Coursenum order by Session_date) as Total_Session 
from( select *, count(*) over (partition by Coursenum order by Session_date) as course_Count 
from( select *,sum(case when courses = 'start' then 1 else 0 end) over (order by Session_date) as Coursenum
from [table_name]
) A)

Refer this link for more information on window functions and this for Case statement