Aggregate and count dates in SQL

Many may have a table in a database, where they also have one or more date fields. If you ever have the wish to count the number of occurrences of each day, read on for info on how to write SQL for just this.

Sometimes I wish SQL had a function to do just the thing I want it to do. Most of the times it doesn't, so I need to write something much more complex from the existing functions.

My first experimentation by reading SQL reference info produced this; (SQL Server flavour)

select DISTINCT(
        DATEADD(dd,DATEDIFF(dd,MIN(MYDATE),MYDATE),
            DATEADD(mm,DATEDIFF(mm,MIN(MYDATE),MYDATE),
                DATEADD(yyyy,DATEDIFF(yyyy,MIN(MYDATE),MYDATE),
                    MIN(MYDATE)
        )))) AS MYDAY,
    MYDATE
from MYTABLE

The problem with the above is that it does not work. Besides that, it is way too complex for my liking.  More reading and thinking about the problem produced the following SQL. Although being too complex for my liking, this piece really works.

SELECT DISTINCT(CONVERT(DATE,A.MYDATE,112)) AS MYDAY,
    (SELECT COUNT(*)
    FROM MYTABLE B
    WHERE CONVERT(DATE,B.MYDATE,112)= CONVERT(DATE,A.MYDATE,112)) AS OCCURRENCES
FROM MYTABLE A
ORDER BY MYDAY ASC

Grouping per day give one kind of trend, while an overview per month give another impression:

SELECT DISTINCT(DATEPART(MM,A.MYDATE)) AS MYMONTH,
    (SELECT COUNT(*)
    FROM MYTABLE B
    WHERE DATEPART(MM,B.MYDATE)= DATEPART(MM,A.MYDATE)) AS OCCURRENCES
FROM MYTABLE A
ORDER BY MYMONTH ASC

The CONVERT part is just producing a yyyy-mm-dd format of the date. The DATEPART is picking an element of the date composite data.