Get Last Log time and Count of Occurences on a date and the Last reason from a table

Question

I have a below table

ID  USER    LOG_TIME    REASON
1   PK  2018-07-11 11:00:00 R1, R2
2   PK  2018-07-11 10:00:00 R1
3   AK  2018-07-11 11:00:00 R2
4   PK  2018-07-11 11:30:00 R2
5   AK  2018-07-11 10:00:00 R1
6   PK  2018-07-10 10:00:00 R1, R2
7   AK  2018-07-11 09:00:00 R1, R2
8   AK  2018-07-11 07:00:00 R1
9   PK  2018-07-10 11:00:00 R2
10  AK  2018-07-10 11:00:00 R1, R2

I need the result with the details of the Last Log Time, and the Last Reason and the number of Logs on 2018-07-11. The Query i used is

select USER, max(LOG_TIME) as LAST_LOG, max(REASON) as REASON, case when date_format(LOG_TIME,'%Y-%m-%d')='2018-07-11' then count(*) end as CtLog from LogHistory group by USER order by USER;

The Result i get is being wrong. I need the result as below

USER    LAST_LOG    REASON  CtLog 
PK  2018-07-11 11:30:00 R2  3
AK  2018-07-11 11:00:00 R2  4

What changes are required

You can find the Table and query at http://sqlfiddle.com/#!9/c97574/3

MAX value within a GROUP is asked and answerd so many times here.. Possible duplicate [sql-select-only-rows-with-max-value-on-a-column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Raymond Nijland, Jul 12 '18 at 11:40
Use `where date_format(LOG_TIME,'%Y-%m-%d')='2018-07-11'` instead of `case` statement — Ajay Gupta, Jul 12 '18 at 11:42

score 0 · Answer 1 · answered Jul 12 '18 at 11:37

Here is one method that uses a correlated subquery to get the information for the last record on the day, and then another subquery to get the count:

select lh.*,
       (select count(*)
        from loghistory lh2
        where lh2.user = lh.user and
              lh2.log_time >= '2018-07-11' and
              lh2.log_time < '2018-07-12'
       ) as cnt
from loghistory lh
where lh.log_time = (select max(lh2.log_time)
                     from loghistory lh2
                     where lh2.user = lh.user
                    ) and
      lh.log_time >= '2018-07-11' and
      lh.log_time < '2018-07-12';

You can also do this with an aggregation subquery and join, assuming the times are unique:

select lh.*, lhmax.cnt
from (select user, count(*) as cnt, max(lh.log_time) as max_log_time
      from loghistory lh
      where lh.log_time >= '2018-07-11' and
            lh.log_time < '2018-07-12'
      group by user
     ) lhmax join
     loghistory lh
     on lh.user = lhmax.user and lh.log_time = lhmax.max_log_time;

Get Last Log time and Count of Occurences on a date and the Last reason from a table

1 Answers1