mysql - 组函数的无效使用 - MySQL

Question

运行选择查询时出现此错误，请指教。

Error No : 1111
Error : Invalid use of group function

查询:

SELECT cm.contactid, cm.firstname, cm.surname, cm.mobile, COUNT( * ) total
FROM sales_master as sm, contact_master as cm
WHERE sm.contactid = cm.contactid 
AND cm.mobile != '' AND orderdate>='2012-12-18' AND orderdate<='2013-03-18' 
GROUP BY sm.contactid 
HAVING COUNT(*) >= 1 
ORDER BY COUNT(*) DESC, cm.firstname ASC

Answer 1

像这样更改您的查询。使用 join 而不是 cartisian。如果 cm.orderdate 的类型是 Date，则从以下查询中删除 DATE()。并在 GROUP BY 和 ORDER BY 中使用别名 total而不是 Count(*) 。

SELECT
  cm.contactid,
  cm.firstname,
  cm.surname,
  cm.mobile,
  COUNT(cm.contactid)    total
FROM sales_master as sm
  LEFT JOIN contact_master as cm
    ON sm.contactid = cm.contactid
WHERE 
    AND cm.mobile != ''
    AND DATE(cm.orderdate) >= '2012-12-18'
    AND DATE(cm.orderdate) <= '2013-03-18'
GROUP BY cm.contactid
HAVING total >= 1
ORDER BY total DESC, cm.firstname ASC