how to group records in days based on timestamp value from current date in oracle


Keywords:sql 


Question: 

I Have a table with a column as timestamp with timezone as shown below.

desc log_records:

id number not null,
event_id number not null,
event_desc varchar2(2000),
log_date timestamp with time zone

How do i suppose to group the records like

older than 300 days 12 rows
older than 250 days 12831 rows
older than 200 days 438121 rows 

based on the current date in oracle


1 Answer: 

Kaushik, thank you for sample data. (BTW, I think your solution is wrong, I'll leave a comment).

I think that this is what you're looking for:

SQL> select
  2    sum(case when sysdate - trunc(log_date) > 300 then 1 else 0 end) "older than 300 days",
  3    sum(case when sysdate - trunc(log_date) > 250 then 1 else 0 end) "older than 250 days",
  4    sum(case when sysdate - trunc(log_date) > 200 then 1 else 0 end) "older than 200 days"
  5  from log_records;

older than 300 days older than 250 days older than 200 days
------------------- ------------------- -------------------
                  2                   3                   3

SQL>