Counting rows in SQL stament using grouping by incremnental variables

Question

Hi all

Table I'm trying to get analytical result from is voice calls record. Each call (one row) has duration in seconds (just int value, not datetime). I'm trying to get number of records grouped by 15 seconds spans like this:

+-------------------+
|Period | Count     |
+-------------------+
| 0-15  | 213421    |
|15-30  | 231123    |
|30-45  | 1234      |
+-------------------+

Starts of period is 0, end is 86400.

I've tried some combinations with setting variables for start and end and then incrementing them by 15, but i'm not very successful, and i can't understand what is going wrong.

Please help out. Thank you!

Answer 1

In MySQL:

SELECT  CONCAT(span * 15, '-', span * 15 + 15), COUNT(*) AS cnt
FROM    (
        SELECT  v.*, FLOOR(period / 15) AS span
        FROM    voice_calls v
        ) q
GROUP BY
        span

UPDATE:

The solution you posted will work, assuming there will always be more than 5760 rows.

But you better create a dummy rowset of 5760 rows and use it in OUTER JOIN:

CREATE TABLE spans (span INT NOT NULL PRIMARY KEY);

INSERT
INTO    spans
VALUES  (0),
        (1),
        ...
        (5759)

SELECT  span * 15, COUNT(*)
FROM    spans
LEFT JOIN
        calls
ON      call.duration >= span * 15
        AND call.duration < span * 15 + 15
GROUP BY
        span

It will be more efficient and sane, as it can neither underflow (if less than 5760 rows in calls), nor take to much time if there are millions of rows there.

Answer 2

I think a query like this should work; You'll have to pivot the results yourself on the display though (eg, this query gets the results horizontally, you'll have to display them vertically):

SELECT
    SUM(CASE WHEN CallLength BETWEEN 0 AND 15 THEN CallLength ELSE 0 END) AS ZeroToFifteen,
    SUM(CASE WHEN CallLength BETWEEN 16 AND 30 THEN CallLength ELSE 0 END) AS FifteenToThirty
FROM CallTable

But after re-reading the question, putting case statements up to 86400 is probably out of the question... Oh well :)

Answer 3

This worked at last:

SET @a:=-15, @b:=0;
SELECT  t.start_time, t.end_time, count(c.duration)
FROM    calls c,
        (
        SELECT  (@a:=@a+15) as start_time, (@b:=@b+15) as end_time 
        FROM    `calls`  
        GROUP BY
                 cdr_id
        ) as t
WHERE   c.duration BETWEEN t.start_time and t.end_time
GROUP BY
        t.start_time, t.end_time

Answer 4

The following query fits your needs:

SELECT
  (duration - duration % 15),
  COUNT(*)
FROM
  call
GROUP BY
  (duration - duration % 15)
ORDER BY
  1;

You can also add some string formatting, in case you need the output exactly as you described it:

SELECT
  (duration - duration % 15)::text || '-' || (duration - duration % 15 + 15)::text,
  COUNT(*)
FROM
  call
GROUP BY
  (duration - duration % 15)
ORDER BY
  1;