MySQL Grouping total

Question

I just know the very basics of SQL. I have a database that shows when a person entered an office and when he left.

e.g

Name     Enter                    Exit
Jack     2014-01-01 01:00:00     2014-01-01 02:00:00
Sam      2014-01-01 02:00:00     2014-01-01 03:00:00
Jane     2014-01-01 02:00:00     2014-01-01 03:00:00
Judy     2014-01-01 03:00:00     2014-01-01 04:00:00

What I am trying to do is find out the total number of users that were in the office on an hourly basis, grouped by hours, and maybe grouped by date too, so my result should look like

date           hour   Occupancy
2014-01-01     1       1
2014-01-01     2       3
2014-01-01     3       3
2014-01-01     4       1

****EDIT

I can set the database to only collect hours and discard the minutes, however a person can come in and leave two days after too. So I am looking for something that is not only an hour apart entry and exit.

Answer 1

Updated answer:

Okay, I adjusted your sample data to reflect the cases where Enter and Exit date are not on the same day and Enter and exit times differ in more than one hour.

CREATE TABLE t
    (`Name` varchar(4), `Enter` datetime, `Exit` datetime)
;

INSERT INTO t
    (`Name`, `Enter`, `Exit`)
VALUES
    ('Jack', '2014-01-01 01:00:00', '2014-01-01 02:00:00'),
    ('Sam', '2014-01-01 02:00:00', '2014-01-01 05:00:00'),
    ('Jane', '2014-01-01 02:00:00', '2014-01-01 03:00:00'),
    ('Judy', '2014-01-01 03:00:00', '2014-01-03 04:00:00')
;

To keep things easy I then created a table containing the date range covering our sample data.

drop table if exists n;
create table n(a int auto_increment primary key, b datetime);
insert into n (a) values(null), (null);

insert into n (a)
select null from n n1, n n2, n n3, n n4, n n5, n n6;

This quick hack inserted 66 numbers into our table. Next I generate the dates covering the sample data.

update n 
set b = '2014-01-01 00:00:00' + interval a hour;

Now that we have datetime values for every full hour between 2014-01-01 00:00:00 and 2014-01-03 18:00:00 we can use this table to simply join to it.

select date(n.b), hour(n.b), count(*)
from
t inner join n on n.b between t.Enter and t.Exit
group by 1, 2

This way we get a record for every full hour between the Enter and Exit time. And the result is:

|       DATE | HOUR | COUNT(*) |
|------------|------|----------|
| 2014-01-01 |    1 |        1 |
| 2014-01-01 |    2 |        3 |
| 2014-01-01 |    3 |        3 |
| 2014-01-01 |    4 |        2 |
| 2014-01-01 |    5 |        2 |
| 2014-01-01 |    6 |        1 |
| 2014-01-01 |    7 |        1 |
| 2014-01-01 |    8 |        1 |
| 2014-01-01 |    9 |        1 |
| 2014-01-01 |   10 |        1 |
| 2014-01-01 |   11 |        1 |
| 2014-01-01 |   12 |        1 |
| 2014-01-01 |   13 |        1 |
| 2014-01-01 |   14 |        1 |
| 2014-01-01 |   15 |        1 |
| 2014-01-01 |   16 |        1 |
| 2014-01-01 |   17 |        1 |
| 2014-01-01 |   18 |        1 |
| 2014-01-01 |   19 |        1 |
| 2014-01-01 |   20 |        1 |
| 2014-01-01 |   21 |        1 |
| 2014-01-01 |   22 |        1 |
| 2014-01-01 |   23 |        1 |
| 2014-01-02 |    0 |        1 |
| 2014-01-02 |    1 |        1 |
| 2014-01-02 |    2 |        1 |
| 2014-01-02 |    3 |        1 |
| 2014-01-02 |    4 |        1 |
| 2014-01-02 |    5 |        1 |
| 2014-01-02 |    6 |        1 |
| 2014-01-02 |    7 |        1 |
| 2014-01-02 |    8 |        1 |
| 2014-01-02 |    9 |        1 |
| 2014-01-02 |   10 |        1 |
| 2014-01-02 |   11 |        1 |
| 2014-01-02 |   12 |        1 |
| 2014-01-02 |   13 |        1 |
| 2014-01-02 |   14 |        1 |
| 2014-01-02 |   15 |        1 |
| 2014-01-02 |   16 |        1 |
| 2014-01-02 |   17 |        1 |
| 2014-01-02 |   18 |        1 |
| 2014-01-02 |   19 |        1 |
| 2014-01-02 |   20 |        1 |
| 2014-01-02 |   21 |        1 |
| 2014-01-02 |   22 |        1 |
| 2014-01-02 |   23 |        1 |
| 2014-01-03 |    0 |        1 |
| 2014-01-03 |    1 |        1 |
| 2014-01-03 |    2 |        1 |
| 2014-01-03 |    3 |        1 |
| 2014-01-03 |    4 |        1 |

Of course you'd have to adjust the helping table. It surely is not a bad thing such helping tables around.

Again, here's a sqlfiddle to see it working live.

---

Original answer:

select date(a), hour(a), count(*)
from (
    select enter as a from t
    union all
    select `exit` as a from t
) b
group by 1, 2

• see it working live in an sqlfiddle

Note though, that there are certain assumptions.

1. The times always are to a full hour
2. Enter and Exit date are always on the same day
3. Enter and exit times only differ in one hour

If one of these assumptions is not true, it gets a lot more complex