0

I have a table with the below definition:

CREATE TABLE visit_log ( id INT NOT NULL , door_entry TIMESTAMP NOT NULL);  

This table has the below records:

insert into visit_log values(1, '2009-04-15 15:00.000');
insert into visit_log values(1, '2009-04-15 15:20.000');
insert into visit_log values(1, '2009-04-15 15:55.000');
insert into visit_log values(2, '2009-04-15 15:00.000');
insert into visit_log values(2, '2009-04-15 15:40.000');
insert into visit_log values(2, '2009-04-15 16:20.000');
insert into visit_log values(3, '2009-04-15 15:00.000');  

Now I want to find that how many times a person has visit_log within each 30 minutes, I mean all the visits at the range of 30 minutes should be counted as 1 visit, my desired result is shown below:

id         visits  
 1            2         
 2            3  
 3            1 

id 1 gets two visits because the first two visits are counted as one visit as they happened within the time span of 30 minutes, id 2 has three visits because all of three visits came in the time difference of more than 30 minutes and the id 3 has one visit.

Any help is appreciated in advance.

0

This query will give you the answer you are looking for:

select s.id, count(*) as visits from 
(select distinct id, concat(hour(door_entry), floor(minute(door_entry)/30)) 
from visit_log) s group by s.id
2
  • @AbdulRaheemGhani - Well, it's not exactly what you asked for. Entries at 09:28 and 09:33 will give a count of 2. To fix this would lead to much more complex code and potentially an ambiguous specification.
    – Rick James
    Apr 30 '19 at 1:36
  • Luis - as an exercise for you, write that query without needing a subquery.
    – Rick James
    Apr 30 '19 at 1:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.