Interval in minutes of a period

Question

Interval in minutes of a period

Navigation

#1 by (2 votes)

3

I'm trying to make a select that lists in a range of 10 minutes a value of the bank, is there any way to show the data even though it does not have data in the range correctly?

Example :

00:00:00 = 0

00:10:00 = 0

00:20:00 = 0

06:00:00 = 5

06:10:00 = 70

 SELECT 
    datetime_entry_queue,SUM(duration) AS segundos,
    sec_to_time(SUM(duration)) AS horas, 
    COUNT(callerid) AS tot_chamadas
FROM
    call_center.call_entry
WHERE
 GROUP BY
    UNIX_TIMESTAMP(datetime_entry_queue) DIV 600;

Data select :

    CREATE TABLE 'call_entry' (
  'id' int(10) unsigned NOT NULL auto_increment,
  'id_agent' int(10) unsigned default NULL,
  'id_queue_call_entry' int(10) unsigned NOT NULL,
  'id_contact' int(10) unsigned default NULL,
  'callerid' varchar(15) NOT NULL,
  'datetime_init' datetime default NULL,
  'datetime_end' datetime default NULL,
  'duration' int(10) unsigned default NULL,
  'status' varchar(32) default NULL,
  'transfer' varchar(6) default NULL,
  'datetime_entry_queue' datetime default NULL,
  'duration_wait' int(11) default NULL,
  'uniqueid' varchar(32) default NULL,
  'id_campaign' int(10) unsigned default NULL,
  'trunk' varchar(20) NOT NULL,
  PRIMARY KEY  ('id'),
  KEY 'id_agent' ('id_agent'),
  KEY 'id_queue_call_entry' ('id_queue_call_entry'),
  KEY 'id_contact' ('id_contact'),
  KEY 'call_entry_ibfk_4' ('id_campaign'),
  KEY 'datetime_init' ('datetime_init'),
  KEY 'datetime_entry_queue' ('datetime_entry_queue'),
  CONSTRAINT 'call_entry_ibfk_1' FOREIGN KEY ('id_agent') REFERENCES 'agent' ('id'),
  CONSTRAINT 'call_entry_ibfk_2' FOREIGN KEY ('id_queue_call_entry') REFERENCES 'queue_call_entry' ('id'),
  CONSTRAINT 'call_entry_ibfk_3' FOREIGN KEY ('id_contact') REFERENCES 'contact' ('id'),
  CONSTRAINT 'call_entry_ibfk_4' FOREIGN KEY ('id_campaign') REFERENCES 'campaign_entry' ('id')
) ENGINE=InnoDB AUTO_INCREMENT=966709 DEFAULT CHARSET=utf8;
   datetime_entry_queue BETWEEN '2016-10-01' AND '2016-10-15'

php mysql sql

asked by anonymous 21.11.2016 / 14:45

1 answer

Break loop in java side menu with left margin

score 2 · Accepted Answer

The problem is the first field of your query . datetime_entry_queue ends up harming the pool. Please remove it for grouped results:

SELECT SUM(duration) AS segundos,
       SEC_TO_TIME(SUM(duration)) AS horas, 
       COUNT(callerid) AS tot_chamadas
  FROM call_center.call_entry
 WHERE datetime_entry_queue BETWEEN '2016-10-01' AND '2016-10-15'
 GROUP BY UNIX_TIMESTAMP(datetime_entry_queue) DIV 600;

If you need this field use subquery :

SELECT x.intervalo,
       SUM(x.duration) AS segundos,
       SEC_TO_TIME(SUM(x.duration)) AS horas,
       COUNT(x.callerid) AS tot_chamadas
  FROM (SELECT UNIX_TIMESTAMP(ce.datetime_entry_queue) DIV 600 AS intervalo,
               ce.duration, 
               ce.callerid
          FROM call_center.call_entry ce
         WHERE ce.datetime_entry_queue BETWEEN '2016-10-01' AND '2016-10-15') x
 GROUP BY x.intervalo;