I'm not real sure why you're doing the math acrobatics, seems to me that a simple, brute force, method might be:
create a table of days of the month for a year:
create table DaysOMonth (monthday date, daycnt int)
declare @days date = '2020-01-01'
declare @End date = '2021-01-01'

while ( @days < @End )
begin
INSERT INTO DaysOMonth (monthday, daycnt) VALUES( @days, 0 )
SELECT @days = dateadd(DAY, 1, @days )
END

then, in your SQL
;with ThisCnt as (
Select DATE_OPEN, count(*) as daycnt
from TableName
group by DATE_OPEN)
Update DaysOMonth set daycnt = (select daycnt from ThisCnt where date_open = monthday)


That should give you the date with the counts for days that have records.

>Hi,
>
>How do you count number of records, in a SQL Select, by the DAY of the MONTH? For example, say the table has a field DATE_OPEN which can be any DATE of a month. For example, May of 2020. I need to have the total number of records for each DAY of the month. Note that some DAYs have no records, so the result in the query is 0.
>Here is my attempt (not successful):
>

>COUNT(DAY(DATE_OPEN)=1) AS DAY01
>COUNT(DAY(DATE_OPEN)=2) AS DAY02
>COUNT(DAY(DATE_OPEN)=3) AS DAY03
>COUNT(DAY(DATE_OPEN)=4) AS DAY04
>and so on until DAY30 or DAY31
>

>
>TIA.
>
>UPDATE. I think what is missing is GROUP BY. But I am not sure what to put into the GROUP BY.