Select Maximum Count From Group Of Events

Question

The SQL script

select eventid_nbr, trunc(received_date, 'DD'), sentindicator, count(eventid_nbr) as count
from eventlog
where received_date between to_date('2017-07-01, 10:00 A.M.','YYYY-MM-DD, HH:MI A.M.') and to_date('2017-07-01, 11:00 A.M.','YYYY-MM-DD, HH:MI A.M.')
group by eventid_nbr, trunc(received_date, 'DD'), sentindicator
order by count desc, trunc(received_date, 'DD');

has output

EVENT  RECEIVED_DATE  SENTINDICATOR   COUNT

1      01-JUL-17      Y               128
1      01-JUL-17      E1              2
104    01-JUL-17      Y               55
105    01-JUL-17      Y               4
106    01-JUL-17      Y               3

whereby I need the maximum count chosen amongst each event whereby the output would appear as

 EVENT  RECEIVED_DATE  SENTINDICATOR   COUNT

 1      01-JUL-17      Y           128
 104    01-JUL-17      Y           55
 105    01-JUL-17      Y           4
 106    01-JUL-17      Y           3

For each grouping, would I need to select max(count)? How could I achieve this?

I've updated the question indicating I need to select all events (possibly 200) containing duplicates as above with event 1, and include only that tuple whose column COUNT is a MAX(COUNT). For non-duplicated events, such as 104, 105, and 106, their tuples are included in the result set whereby their counts are already a MAX(COUNT) — Mushy, Aug 11 '17 at 12:58

score 0 · Answer 1 · answered Aug 11 '17 at 12:24

In Oracle 12c, you can do:

select eventid_nbr, trunc(received_date, 'DD'), sentindicator, 
       count(eventid_nbr) as count
from eventlog
where received_date between to_date('2017-07-01, 10:00 A.M.','YYYY-MM-DD, HH:MI A.M.') and
                            to_date('2017-07-01, 11:00 A.M.','YYYY-MM-DD, HH:MI A.M.')
group by eventid_nbr, trunc(received_date, 'DD'), sentindicator
order by count desc, trunc(received_date, 'DD')
fetch first 1 row only;

In earlier versions, a subquery does the same thing:

select el.*
from (select eventid_nbr, trunc(received_date, 'DD'), sentindicator, 
             count(eventid_nbr) as count
      from eventlog
      where received_date between to_date('2017-07-01, 10:00 A.M.','YYYY-MM-DD, HH:MI A.M.') and
                                  to_date('2017-07-01, 11:00 A.M.','YYYY-MM-DD, HH:MI A.M.')
      group by eventid_nbr, trunc(received_date, 'DD'), sentindicator
      order by count desc, trunc(received_date, 'DD')
     ) el
where rownum = 1;

Can you please elaborate more based upon an edit to the question to bring further clarification? — Mushy, Aug 11 '17 at 12:59

score 0 · Answer 2 · answered Aug 11 '17 at 12:47

select eventid,dt,sentindicator,max(count)
from 
 (
  select eventid_nbr, trunc(received_date, 'DD') as dt, sentindicator, 
  count(eventid_nbr) as count
  from eventlog
  where received_date between to_date('2017-07-01, 10:00 A.M.','YYYY-MM-DD, 
  HH:MI A.M.') 
  and to_date('2017-07-01, 11:00 A.M.','YYYY-MM-DD, HH:MI A.M.'
  )
    group by eventid_nbr, trunc(received_date, 'DD'), sentindicator
    order by count desc, trunc(received_date, 'DD'))a
group by eventid,dt,sentindicator

just add an outer query with a max(count) as an outer query to your query — Aparna, Aug 11 '17 at 12:48

score 0 · Answer 3 · answered Aug 11 '17 at 13:01

select eventid_nbr, received_day, max(sentindicator) keep(dense_rank last order by count), max(count) as count
from (select eventid_nbr, trunc(received_date, 'DD') as received_day,  sentindicator, count(*) as count
        from eventlog
       where received_date between to_date('2017-07-01, 10:00 A.M.','YYYY-MM-DD, HH:MI A.M.') and to_date('2017-07-01, 11:00 A.M.','YYYY-MM-DD, HH:MI A.M.')
       group by eventid_nbr, trunc(received_date, 'DD'), sentindicator)
group by eventid_nbr, received_day
order by 4 desc, received_day;

Select Maximum Count From Group Of Events

3 Answers3