CODEWITHSUNDEEP
sql
n179911
n179911

Reputation: 20301

How to 'Group By' date in SQL

My data has 2 columns,

  Time                   | Name
  -----------------------+-----
  6/9/2020 7:40:31 PM    | A
  6/9/2020 7:52:51 PM    | B
  6/9/2020 8:31:03 PM    | C
  6/10/2020 9:21:00 PM   | A
  6/11/2020 12:08:06 PM  | A

How can I aggregate the data so that I can count how many entry per day (not to second of the date) something like:

  Time        | Count
  ------------+------
  6/9/2020    | 3
  6/10/2020   | 1
  6/11/2020   | 1

Upvotes: 0

Views: 51

Answers (3)

Hilarion
Hilarion

Reputation: 870

You have not specified which database engine you are using and what data types you are using. (Are your date/time columns actually date/time, or maybe text?)

Assuming you are using Oracle and the Time column is of type DATE, the solution could be:

SELECT TRUNC("Time") AS "Time", COUNT(*) AS "Count"
  FROM SomeTable
 GROUP BY TRUNC("Time")

Upvotes: 0

Denis Kohl
Denis Kohl

Reputation: 750

For Oracle: select to_char(Time, 'MM/DD/YYYY'), count from <table> group by to_char(Time, 'MM/DD/YYYY');

Upvotes: 0

Isaac
Isaac

Reputation: 3363

You need to strip off the time. In SQL Server it would look like this.

SELECT CONVERT(DATE, Time) as [Time], COUNT(*)
FROM YourTable
GROUP BY CONVERT(DATE, Time)

Upvotes: 1

Related Questions