Interval in minutes of a period

Question

I’m trying to make a select listing in an interval of 10 minutes a bank value, only this way it appears carefree, there is some way to show the data even if you don’t 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;

Dice 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'

Answer 1

The problem is the first field of your query. The datetime_entry_queue ends up harming the grouping. Remove it to get the results grouped:

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 a 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;