Ahmad Saleh - 28 days ago 5x
MySQL Question

# Getting Average Count Between Datetime From/To Specific Hours

I would like to make a statistic for the record on my database where I want to calculate the average number when the user login to the system from/to certain datetime and in each 4 hours per day

simple example: I want to get the average of successful login from '2016-09-20 00:00:00' to '2016-09-23 23:59:59' where the result should be given on these certain times ('00:00:00' - '11:59:59') and ('12:00:00' - '23:59:59')

This is a list of an example data (where status 1 means success, 0 meant not):

``````| id | | driver_id |    login_timedate     | status |
|  1 | |    1    | '2016-09-20 00:00:11' |    1   |
|  2 | |    2    | '2016-09-20 01:16:09' |    1   |
|  3 | |    2    | '2016-09-20 23:01:16' |    1   |
|  4 | |    3    | '2016-09-21 04:04:59' |    1   |
|  5 | |    3    | '2016-09-21 05:06:59' |    0   |
|  6 | |    2    | '2016-09-21 16:06:59' |    1   |
|  7 | |    1    | '2016-09-22 00:16:59' |    1   |
|  8 | |    2    | '2016-09-23 04:09:22' |    0   |
|  9 | |    1    | '2016-09-23 06:22:59' |    1   |
| 10 | |    3    | '2016-09-23 22:09:22' |    1   |
| 11 | |    1    | '2016-09-24 00:00:22' |    1   |
``````

• So in this case I'll get total number of success login from (20-23 / 09 / 2016) are: 8 (day1= 3 , day2= 2 , day3= 1 , day4= 2)

• Total number of success each day within the range from ('00:00:00' - '11:59:59') are 5 (day1= 2 , day2= 1 , day3= 1 , day4= 1)

• Average: 5 / 4 = 1.25

• Total number of success each day within the range from ('00:00:00' - '11:59:59') are 3 (day1= 1 , day2= 0 , day3= 1 , day4= 1)

• Average: 3 / 4 = 0.75

I have did the first part to get the total number of success login within datetime range this is my code (which will return 8)

``````SET @start_date = '2016-09-20';
SET @start_taime = '00:00:00';

SET @end_date = '2016-09-23';
SET @end_time = '23:59:59';

SELECT   COUNT( `login_logs`.`driver_id` ) AS  `number_of_success`
AND
AND
``````

#Update:
Expected Output for this code:

``````| total_logins |
|       8      |
``````

I would like to do the next part which calculate the average logins within the same datetime range from XX:XX:XX time to YY:YY:YY time such as this:

• Total number of success each day within the range from ('00:00:00' - '11:59:59') are 5 (day1= 2 , day2= 1
, day3= 1 , day4= 1)

• Average: 5 / 4 = 1.25

#Update:
Expected Output After modifying my code to get avrage from ('00:00:00' - '11:59:59') :

``````|  Avrage_00_12   |
|       1.25      |
``````

How should I modify the code to implement this part?

I hope that you understood my question

You can use the following query:

``````SELECT SUM(`number_of_success`) AS `total_success`,
SUM(`success_range1`) / COUNT(*) AS `average1`,
SUM(`success_range2`) / COUNT(*) AS `average2`
FROM (
COUNT(CASE
BETWEEN '00:00:00' AND '11:59:59'
THEN 1
END) AS `success_range1`,
BETWEEN '12:00:00' AND '23:59:59'
THEN 1
END) AS `success_range2`
AND
``````total_success, average1, average2