Oracle SQL - count by day - not a GROUP BY expression

user_11572712 0
Viewed 101

I have data which looks like this:

TIME ID 
29/11/20 13:45:33,810000000 1234
06/01/21 13:45:33,810000000 5678
06/01/21 14:05:33,727000000 5678

That means, I have a column TIME and ID. What I want to do is to count all the entries by day and all the distinct IDs per day.

As result I would like to get this:

DAY COUNT(*) distinctID
29/11/20 1 1
06/01/21 2 1

I did this:

select trunc(to_char(TIME, ‘HH’),'DD/MM/YY'), 
COUNT(*), count(distinct ID) as distinctID from CDRHEADER 
where TIME>= date '2021-03-01'
group by trunc(TIME,'DD/MM/YY')
order by trunc(TIME,'DD/MM/YY');

As error I get: not a GROUP BY Expression.

Furthermore, I am also not sure about the date operations I executed and if they are correct.

NOTE: I would like to use the date entries as date values and not compare strings or something like this.

How can I get what I expect?

sql-order-by oracle sql
1 Answers

Hmmm . . . I think you want:

select trunc(time) as the_date, 
       count(*), count(distinct ID) as distinctID
from CDRHEADER 
where time >= date '2021-03-01'
group by trunc(time)
order by trunc(time);

I'm not sure why you are using to_char() or 'HH'. If you really want to output the time as 'DD/MM/YYYY', then:

select to_char(trunc(time), 'DD/MM/YYYY') as the_date, 
       count(*), count(distinct ID) as distinctID
from CDRHEADER 
where time >= date '2021-03-01'
group by trunc(time)
order by trunc(time);
user_11440350
Related