mysql - 需要解决 mysql 查询问题

Question

亲们

当我运行下面的查询时，我收到无效使用 group by 函数的错误

    SELECT `Margin`.`destination`, 
ROUND(sum(duration),2) as total_duration, 
sum(calls) as total_calls 
FROM `ilax`.`margins` AS `Margin` 
 WHERE `date1` = '2013-08-30' and `destination` like "af%"  
AND ROUND(sum(duration),2) like "3%"  
group by `destination` 
ORDER BY duration Asc LIMIT 0, 20;

让我知道解决方法

Answer 1

WHERE 子句在分组发生之前 求值，因此不能在其中使用SUM()；请改用 HAVING 子句，该子句在分组后求值:

SELECT   destination,
         ROUND(SUM(duration), 2) AS total_duration,
         SUM(calls)              AS total_calls
FROM     ilax.margins
WHERE    date1 = '2013-08-30'
     AND destination LIKE 'af%'
GROUP BY destination
HAVING   total_duration LIKE '3%'
ORDER BY total_duration ASC
LIMIT    0, 20

另请注意，确实应该对数值使用数字比较操作，而不是字符串模式匹配。例如:

HAVING   total_duration >= 3000 AND total_duration < 4000