Search code examples
mysqlsqlcountdistinct

I am trying to use SQL to count the number of rows for each date in my database

My database is something like this:

Date                        Col2         Col3       Col4
2020-11-02 06:45:16.000     data         data       data
2020-11-02 07:23:23.111     data         data       data
2020-12-14 08:55:44.213     data         data       data

I only need the counts of the dates like this:

Date          Count
2020-11-02     2
2020-12-14     1

I've tried ...

select distinct(Date),
       count(*)

from database

... but the date format is not my friend. Any ideas would be appreciated.

Solution

  • In most databases, you can remove the date component by converting to a date and using group by:

    select cast(Date as date), count(*)    
from database
group by cast(Date as date)
order by cast(Date as date);

    However, various databases also have other methods to do the conversion, such as:

    • date(date) in MySQL.
    • trunc(date) in Oracle.
    • date_trunc('day', date) in Postgres.