Ahmad Saleh Ahmad Saleh - 2 months ago 10
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 SUM(`total_logins`.`number_of_success`) FROM (
SELECT COUNT( `login_logs`.`driver_id` ) AS `number_of_success`
FROM `login_logs`
WHERE `login_logs`.`status` = 1
AND
`login_logs`.`login_timedate` >= CONCAT(@start_date, ' ', @start_time)
AND
`login_logs`.`login_timedate` <= CONCAT(@end_date, ' ', @end_time)
GROUP BY `login_logs`.`user_id`
) AS `total_logins`





#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

thank you for your help in advanced

Answer

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 (
   SELECT DATE(`login_logs`.`login_timedate`), 
          COUNT( `login_logs`.`driver_id` ) AS  `number_of_success`,
          COUNT(CASE 
                   WHEN TIME(`login_logs`.`login_timedate`) 
                        BETWEEN '00:00:00' AND '11:59:59' 
                   THEN 1 
                END) AS `success_range1`,
          COUNT(CASE WHEN TIME(`login_logs`.`login_timedate`) 
                          BETWEEN '12:00:00' AND '23:59:59' 
                     THEN 1 
                END) AS `success_range2`
   FROM  `login_logs` 
   WHERE  `login_logs`.`status` = 1
          AND
          `login_logs`.`login_timedate` >= '2016-09-20 00:00:00'
          AND
          `login_logs`.`login_timedate` <= '2016-09-23 23:59:59'
   GROUP BY  DATE(`login_logs`.`login_timedate`)) AS t

Output:

total_success, average1, average2
----------------------------------
8,             1.2500,   0.7500