How to get count of particular column value from total number of records and display difference in two different columns in SQL Server

I am trying to get difference between total records and a column (Is_Registered) to get Month wise matrics of how many registered in particular month and how many are pending

Actual Data

| Inserted On | IsRegistered |
+-------------+--------------+
| 10-01-2020  | 1            |
| 15-01-2020  | 1            |
| 17-01-2020  | null         |
| 17-02-2020  | 1            |
| 21-02-2020  | null         |   
| 04-04-2020  | null         |
| 18-04-2020  | null         |
| 19-04-2020  | 1            |

Expected Output -As shown in actual data, out of 8 users(records) 2 are registered in Jan and 6 are not ,in February total 3 are registered i.e. Jan’s 2 + Feb’s 1 and 5 are not and so on

| Year     | Month          | Registered  | Not Registered |
| -------- | -------------- | ----------- | -------------- |
| 2020     | January        | 2           | 6              |
| 2020     | Feb            | 3           | 5              |
| 2020     | April          | 4           | 4              |

But when a new record is added with new month then it should not update previous output result e.g. After adding new record with month as May and IsReg as NULL the value for Not_Registered should be as mentioned below because the new record is added in new month.

| Year     | Month          | Registered  | Not Registered |
| -------- | -------------- | ----------- | -------------- |
| 2020     | January        | 2           | 6              |
| 2020     | Feb            | 3           | 5              |
| 2020     | April          | 4           | 4              |
| 2020     | May            | 4           | 5              |

And if the new record has month as May and Is_Registered as 1(true) then the output should be as follows

| Year     | Month          | Registered  | Not Registered |
| -------- | -------------- | ----------- | -------------- |
| 2020     | January        | 2           | 6              |
| 2020     | Feb            | 3           | 5              |
| 2020     | April          | 4           | 4              |
| 2020     | May            | 5           | 4              |

I managed to write a query but didn’t got expected output, what changes I’ll have to make in order to get expected output

select year(dateinserted) as [Year], datename(month,dateinserted) as [Month],
coalesce(sum(cast(isregistered as int)), 0) as Authenticated,
sum(case when isregistered is null then 1 else 0 end) as UnAuthenticated
from table_name where IsRegistered is not null
group by year(dateinserted), datename(month,dateinserted)
order by year(dateinserted), month(min(dateinserted));

Output I got after executing above query -

| Year     | Month          | Registered  | Not Registered |
| -------- | -------------- | ----------- | -------------- |
| 2020     | January        | 2           | 1              |
| 2020     | Feb            | 1           | 1              |
| 2020     | April          | 1           | 2              |

Answer

Hmmm . . . You seem to want a cumulative sum of the counts (which are 1 or NULL, so count() works). For the second column, then difference between that and the total number of rows:

select year(dateinserted) as [Year],
       datename(month, dateinserted) as [Month],
       count(isregistered) as registered_in_month,
       sum(count(isregistered)) over (order by min(dateinserted)) as registered_up_to_month,
       sum(count(*)) over () - sum(count(isregistered)) over (order by min(dateinserted)) as not_yet_registered
from table_name 
group by year(dateinserted), datename(month, dateinserted)
order by year(dateinserted), month(min(dateinserted));

Here is a db<>fiddle.