How to find count of the occurrences of a value?

Experts,
In a time column like below, how can I find the occurrence count of all the times?

2018-02-25 23:30:52
2018-02-25 23:30:56
2018-02-25 23:30:56
2018-02-25 23:31:00
2018-02-25 23:31:00
2018-02-25 23:31:04
2018-02-25 23:31:04
2018-02-25 23:31:08
2018-02-25 23:31:08
2018-02-25 23:31:12
2018-02-25 23:31:12
2018-02-25 23:31:15
2018-02-25 23:31:15
2018-02-25 23:31:15

For the above time entries column 'TIME RECORD', I would need something like:

2018-02-25 23:30:52    1
2018-02-25 23:30:56    2
.
.
.
2018-02-25 23:31:15    3

Thanks in advance!
sukhoi35Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Here is my setup:
drop table tab1 purge;
create table tab1(time_record date);
insert into tab1 values(to_date('2018-02-25 23:30:52','YYYY-MM-DD HH24:MI:SS'));
insert into tab1 values(to_date('2018-02-25 23:30:56','YYYY-MM-DD HH24:MI:SS'));
insert into tab1 values(to_date('2018-02-25 23:30:56','YYYY-MM-DD HH24:MI:SS'));
insert into tab1 values(to_date('2018-02-25 23:31:00','YYYY-MM-DD HH24:MI:SS'));
insert into tab1 values(to_date('2018-02-25 23:31:00','YYYY-MM-DD HH24:MI:SS'));
insert into tab1 values(to_date('2018-02-25 23:31:04','YYYY-MM-DD HH24:MI:SS'));
insert into tab1 values(to_date('2018-02-25 23:31:04','YYYY-MM-DD HH24:MI:SS'));
insert into tab1 values(to_date('2018-02-25 23:31:08','YYYY-MM-DD HH24:MI:SS'));
insert into tab1 values(to_date('2018-02-25 23:31:08','YYYY-MM-DD HH24:MI:SS'));
insert into tab1 values(to_date('2018-02-25 23:31:12','YYYY-MM-DD HH24:MI:SS'));
insert into tab1 values(to_date('2018-02-25 23:31:12','YYYY-MM-DD HH24:MI:SS'));
insert into tab1 values(to_date('2018-02-25 23:31:15','YYYY-MM-DD HH24:MI:SS'));
insert into tab1 values(to_date('2018-02-25 23:31:15','YYYY-MM-DD HH24:MI:SS'));
insert into tab1 values(to_date('2018-02-25 23:31:15','YYYY-MM-DD HH24:MI:SS'));
commit;

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
select time_record, count(*) from tab1 group by time_record order by time_record;

Open in new window


The output:
2018-02-25 23:30:52          1
2018-02-25 23:30:56          2
2018-02-25 23:31:00          2
2018-02-25 23:31:04          2
2018-02-25 23:31:08          2
2018-02-25 23:31:12          2
2018-02-25 23:31:15          3

Open in new window


If the column isn't a DATE data type, what date type is it?
0
 
slightwv (䄆 Netminder) Commented:
What database?

It should be:
select time_record, count(*) from some_table group by time_record;

Without knowing more it is hard to provide exact SQL.
0
 
sukhoi35Author Commented:
Thanks Netminder, this is oracle db. Will try your query.
0
 
sukhoi35Author Commented:
Thank you Netminder! I am able to see what I wanted.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.