Thank you for your input. But I found the solution in using the SUM, as:

SELECT SUM(IIF( DAY(DATE_OPEN)=1,1,0)) as DAY01, ;
	   SUM(IIF( DAY(DATE_OPEN)=2,1,0)) as DAY02, ;
	   SUM(IIF( DAY(DATE_OPEN)=3,1,0)) as DAY03, ; 
	   SUM(IIF( DAY(DATE_OPEN)=4,1,0)) as DAY04, ;
	   SUM(IIF( DAY(DATE_OPEN)=5,1,0)) as DAY05, ;
	   SUM(IIF( DAY(DATE_OPEN)=6,1,0)) as DAY06 ;
*-- and so on

>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.