Oracle sql query to show count of events daily and total number of events for last seven days

Question

There is the following table: event_table, which contains information about object_name, event_number, event_supplementary_info, event_time. I would like to have a sql query, which shows number of events per last seven day daily and total number.

I need something like this

select Object_name, event_number max(decode(trim(dow),'MONDAY',totalquantity,0)) Mon, max(decode(trim(dow),'TUESDAY',totalquantity,0)) Tue, max(decode(trim(dow),'WEDNESDAY',totalquantity,0)) Wed, max(decode(trim(dow),'THURSDAY',totalquantity,0)) Thu, max(decode(trim(dow),'FRIDAY',totalquantity,0)) Fri, max(decode(trim(dow),'SATURDAY',totalquantity,0)) Sat, max(decode(trim(dow),'SUNDAY',totalquantity,0)) Sun,
( max(decode(trim(dow),'MONDAY',totalquantity,0)) + max(decode(trim(dow),'TUESDAY',totalquantity,0)) + max(decode(trim(dow),'WEDNESDAY',totalquantity,0)) + max(decode(trim(dow),'THURSDAY',totalquantity,0)) + max(decode(trim(dow),'FRIDAY',totalquantity,0)) + max(decode(trim(dow),'SATURDAY',totalquantity,0)) + max(decode(trim(dow),'SUNDAY',totalquantity,0)) ) TOTAL from
( select event_name,
to_char(event_time, 'DAY') as dow,
sum(event_time) as totalquantity from event_table a where a.event_time >= trunc(sysdate-7,'D') and a.tradedate <= trunc(sysdate-7,'D') + 7 group by a.event_name, alarm_time ) group by Object_name, event_number;

Can you please help?

Answer 1

Or maybe this is what you are looking for: http://psoug.org/reference/rollup.html ?

Answer 2

Here is a try using this sample data. For next time please provide some sample data yourself.

SQL> create table event_table (event_number,object_name,event_time,event_supplementary_info)
  2  as
  3  select 1, 'A', sysdate - 7, 'info' from dual union all
  4  select 1, 'B', sysdate - 7, 'info' from dual union all
  5  select 1, 'B', sysdate - 7, 'info' from dual union all
  6  select 2, 'A', sysdate - 6, 'info' from dual union all
  7  select 2, 'B', sysdate - 6, 'info' from dual union all
  8  select 2, 'B', sysdate - 6, 'info' from dual union all
  9  select 3, 'A', sysdate - 5, 'info' from dual union all
 10  select 3, 'A', sysdate - 5, 'info' from dual union all
 11  select 4, 'C', sysdate - 4, 'info' from dual union all
 12  select 4, 'C', sysdate - 4, 'info' from dual union all
 13  select 4, 'C', sysdate - 4, 'info' from dual union all
 14  select 4, 'C', sysdate - 4, 'info' from dual union all
 15  select 4, 'C', sysdate - 4, 'info' from dual union all
 16  select 4, 'D', sysdate - 4, 'info' from dual union all
 17  select 5, 'A', sysdate - 3, 'info' from dual union all
 18  select 6, 'D', sysdate - 2, 'info' from dual union all
 19  select 6, 'D', sysdate - 2, 'info' from dual union all
 20  select 7, 'A', sysdate - 1, 'info' from dual union all
 21  select 7, 'A', sysdate - 1, 'info' from dual union all
 22  select 7, 'A', sysdate - 1, 'info' from dual union all
 23  select 7, 'A', sysdate - 1, 'info' from dual
 24  /

Table created.

Your query doesn't work: there are superfluous right brackets. If I remove them, it still doesn't work because you are comparing dates with varchar2's:

SQL> select object_name,
  2         event_number,
  3         count(*),
  4         event_supplementary_info
  5    from event_table
  6   where event_time between to_char(sysdate -7, 'YYYY-MM-DD')
  7                        and to_char(sysdate , 'YYYY-MM-DD')
  8  group by object_name, event_number, event_supplementary_info
  9  /

no rows selected

So my base query is this one where I compare the dates with dates:

SQL> select object_name
  2       , event_number
  3       , count(*)
  4       , event_supplementary_info
  5    from event_table
  6   where event_time between sysdate -7 and sysdate
  7   group by object_name
  8       , event_number
  9       , event_supplementary_info
 10   order by object_name
 11       , event_number
 12  /

O EVENT_NUMBER   COUNT(*) EVEN
- ------------ ---------- ----
A            1          1 info
A            2          1 info
A            3          2 info
A            5          1 info
A            7          4 info
B            1          2 info
B            2          2 info
C            4          5 info
D            4          1 info
D            6          2 info

10 rows selected.

I interpreted your question that you want a record with the total number of events per object_name. With this sample data, you need 4 extra records, for objects A, B, C and D. To achieve that, I added another grouping set on just object_name. And I included trunc(event_time) to the existing grouping set for clarity.

SQL> select object_name
  2       , event_number
  3       , count(*)
  4       , event_supplementary_info
  5       , trunc(event_time)
  6    from event_table
  7   where event_time between sysdate -7 and sysdate
  8   group by grouping sets
  9         ( ( object_name
 10           , event_number
 11           , event_supplementary_info
 12           , trunc(event_time)
 13           )
 14         , ( object_name )
 15         )
 16   order by object_name
 17       , event_number
 18  /

O EVENT_NUMBER   COUNT(*) EVEN TRUNC(EVENT_TIME)
- ------------ ---------- ---- -------------------
A            1          1 info 15-08-2010 00:00:00
A            2          1 info 16-08-2010 00:00:00
A            3          2 info 17-08-2010 00:00:00
A            5          1 info 19-08-2010 00:00:00
A            7          4 info 21-08-2010 00:00:00
A                       9
B            1          2 info 15-08-2010 00:00:00
B            2          2 info 16-08-2010 00:00:00
B                       4
C            4          5 info 18-08-2010 00:00:00
C                       5
D            4          1 info 18-08-2010 00:00:00
D            6          2 info 20-08-2010 00:00:00
D                       3

14 rows selected.

The short way to write such a query, is to convert the grouping sets to a rollup:

SQL> select object_name
  2       , event_number
  3       , count(*)
  4       , event_supplementary_info
  5       , trunc(event_time)
  6    from event_table
  7   where event_time between sysdate -7 and sysdate
  8   group by object_name
  9       , rollup ((event_number,event_supplementary_info,trunc(event_time)))
 10   order by object_name
 11       , event_number
 12  /

O EVENT_NUMBER   COUNT(*) EVEN TRUNC(EVENT_TIME)
- ------------ ---------- ---- -------------------
A            1          1 info 15-08-2010 00:00:00
A            2          1 info 16-08-2010 00:00:00
A            3          2 info 17-08-2010 00:00:00
A            5          1 info 19-08-2010 00:00:00
A            7          4 info 21-08-2010 00:00:00
A                       9
B            1          2 info 15-08-2010 00:00:00
B            2          2 info 16-08-2010 00:00:00
B                       4
C            4          5 info 18-08-2010 00:00:00
C                       5
D            4          1 info 18-08-2010 00:00:00
D            6          2 info 20-08-2010 00:00:00
D                       3

14 rows selected.

Regards, Rob.