CODEWITHSUNDEEP
mysql
John Biddle
John Biddle

Reputation: 2682

Counting Records within a time duration

I have a table of pool league match results with both match_begin_time and match_end_time fields of type TIME.

I want to know how many records fall into various time duration intervals, i.e., < 1 Hour, 1 Hour to 1.5 Hours, etc.

I tried the following code to grab the 1st 2 intervals, but each one returned all the records irrespective of the time interval between Begin and End:

{

SELECT
     COUNT(TIMEDIFF(match_end_time, match_begin_time) BETWEEN time("00:00:00") AND time("00:59:59")) AS "< 1 Hour",
     COUNT(TIMEDIFF(match_end_time, match_begin_time) BETWEEN time("01:00:00") AND time("01:30:00")) AS "1 - 1.5 Hours"
FROM   lwljhb_lwl_matches
WHERE
     match_end_time > 0 AND
     match_end_time > 0 AND
     match_end_time > Match_begin_time
}

Using the WHERE clause I can run the query once for each time interval and I get the right answer. For example, the following code correctly returns the 4 records where the duration is < 1 hour.

{

SELECT 
     TIMEDIFF(match_end_time, match_begin_time) AS "< 1 Hour"
FROM   lwljhb_lwl_matches
WHERE
     match_end_time > 0 AND
     match_end_time > 0 AND
     match_end_time > Match_begin_time AND
     TIMEDIFF(match_end_time, match_begin_time) BETWEEN time("00:00:00") AND time("00:59:59")
}

Where am I going wrong in my COUNT expressions?

Upvotes: 1

Views: 196

Answers (2)

John Biddle
John Biddle

Reputation: 2682

In MySQL, the boolean expression within my COUNT yields a 1 (true) or a 0 (false). COUNT sees both of these as non_NULL and counts them. I was thinking (incorrectly) that it would only count the 1s (trues). Further research showed me two ways to resolve this, using COUNT & IF or SUM.

COUNT(IF(TIMEDIFF(match_end_time, match_begin_time) BETWEEN time("00:00:00") AND time("00:59:59"), 1, NULL)) AS "
SUM(TIMEDIFF(match_end_time, match_begin_time) BETWEEN time("00:00:00") AND time("00:59:59") AS "

The second eliminates the need for the IF because the booleans are treated as integers and the 1s (trues) increase the sum and the 0s (falses) don't. The NULLs are ignored.

So, my whole query is:

SELECT
     SUM(TIMEDIFF(match_end_time, match_begin_time) BETWEEN '00:00:01' AND '01:00:00' ) AS '<1 Hour',
     SUM(TIMEDIFF(match_end_time, match_begin_time) BETWEEN '01:00:01' AND '01:30:00' ) AS '1 - 1.5 Hours',
     SUM(TIMEDIFF(match_end_time, match_begin_time) BETWEEN '01:30:01' AND '02:00:00' ) AS '1.5 - 2 Hours',
     SUM(TIMEDIFF(match_end_time, match_begin_time) BETWEEN '02:00:01' AND '02:30:00' ) AS '2 - 2.5 Hours',
     SUM(TIMEDIFF(match_end_time, match_begin_time) BETWEEN '02:30:01' AND '03:00:00' ) AS '2.5 - 3 Hours',
     SUM(TIMEDIFF(match_end_time, match_begin_time) BETWEEN '03:00:01' AND '03:30:00' ) AS '3 - 3.5 Hours',
     SUM(TIMEDIFF(match_end_time, match_begin_time) BETWEEN '03:30:01' AND '04:00:00' ) AS '3.5 - 4 Hours',
     SUM(TIMEDIFF(match_end_time, match_begin_time) BETWEEN '04:00:01' AND '10:00:00' ) AS '> 4 Hours'
FROM lwljhb_lwl_matches

Upvotes: 1

Vignesh VS
Vignesh VS

Reputation: 939

I will give you a simple query to display the count of records fall into time interval '< 1 Hour' and '1 Hour to 1.5 Hours'. I think it will helpful for you.

SELECT x.a AS '<1 Hour', y.b AS '1- 1.5 Hours'
FROM (SELECT COUNT(TIMEDIFF (match_end_time, match_begin_time)) AS a
       FROM lwljhb_lwl_matches 
       WHERE TIMEDIFF (match_end_time, match_begin_time)<'01:00:00') as x, 
     (SELECT COUNT(TIMEDIFF (match_end_time, match_begin_time)) AS b
       FROM lwljhb_lwl_matches 
       WHERE TIMEDIFF(match_end_time, match_begin_time) BETWEEN'01:00:00' AND '01:30:00') AS y

You can use COUNT(*) instead of COUNT(TIMEDIFF (match_end_time, match_begin_time)) will produce the same result. Likewise, You can find records of other time intervals. You can check my code in SQLFiddle.

Upvotes: 4

Related Questions