这是我的mysql查询
SELECT IntervalStartTime,IFNULL(SUM(AbandonedCalls),0) AS AbandonedCallSum,SUM(QueueTime) AS QTS,SUM(RingTime) AS RTS,
IFNULL(SUM(AnsweredCalls),0) AS AnsweredCallSum
FROM intervalqueuestatistics
WHERE CallCenterId=17 AND DATE_FORMAT(IntervalStartTime,'%m')=10 AND DATE_FORMAT(IntervalStartTime,'%Y')=2012
GROUP BY DATE_FORMAT(IntervalStartTime,'%d');
现在我想计算一个值(SUM(QueueTime)+SUM(RingTime))/SUM(AnsweredCalls)
所以我相应地修改了我的查询,如下所示
SELECT IntervalStartTime,IFNULL(SUM(AbandonedCalls),0) AS AbandonedCallSum,SUM(QueueTime) AS QTS,SUM(RingTime) AS RTS,
IFNULL(SUM(AnsweredCalls),0) AS AnsweredCallSum,IFNULL(SUM(QueueTime),0) + IFNULL(SUM(RingTime),0)/IFNULL(SUM(AnsweredCalls),0)
FROM intervalqueuestatistics
WHERE CallCenterId=17 AND DATE_FORMAT(IntervalStartTime,'%m')=10 AND DATE_FORMAT(IntervalStartTime,'%Y')=2012
GROUP BY DATE_FORMAT(IntervalStartTime,'%d');
但是当执行时,它并没有给我正确的答案。
例如,此查询返回的行之一
QTS RTS AnsweredCallSum CalculatedField
188000 41645 9 192627.222
但是计算字段是错误的,根据上述计算,它应该是 25516.11
如果 SUM(AnsweredCalls) 为 0,则除以 0,它应该不起作用。
这是我
的猜测:
- 使用
IFNULL(..., 1)
,这样如果存在 null 列,您永远不会除以0
。 - 显式使用括号,以便操作顺序不会失败。(你目前在
sum1 + (sum2 / sum3)
,而我认为你想要(sum1 + sum2) / sum3
)。请记住,乘法和除法在加法和减法之前,不带括号。
( IFNULL(SUM(QueueTime), 0) + IFNULL(SUM(RingTime), 0) )
/ IFNULL(SUM(AnsweredCalls), 1)