CODEWITHSUNDEEP
sql
Arun Unnikrishnan
Arun Unnikrishnan

Reputation: 99

SQL: Split and Group By date and time

Part of the table.

Reg_Date
2021-06-18 08:53:19.000
2021-06-18 08:53:31.000
2021-06-18 08:53:32.000
2021-06-18 08:54:57.000
2021-06-18 08:55:07.000

Expected Result

Reg_Date Registration_Count
2021-06-18 43
2021-06-18 23
2021-06-18 2
2021-06-18 65
2021-06-18 7

I am trying to count the number of occurrence of the date. The query below is just to indicate the desired result.

select split(reg_date,' '), count(reg_date) from dbo.table group by reg_date

Upvotes: 0

Views: 704

Answers (2)

Viktor Török
Viktor Török

Reputation: 1319

I think you use SQL Server. In this SQL dialect instead of the split function you should cast the datetime value:

cast(reg_date As Date)

Upvotes: 1

juergen d
juergen d

Reputation: 204746

You need to group by only the dates and not the datetimes

select cast(reg_date as date) as Reg_Date, 
       count(*) as Registration_Count
from dbo.table 
group by cast(reg_date as date)

Upvotes: 2

Related Questions