MYSQL DB 错误 SELECT 列表的表达式 #2 不在 GROUP BY 子句中并且包含非聚合列

Question

SELECT CUST_ID, AVG(freq), AVG(amount) ,month from 
(SELECT CUST_ID, DATE_FORMAT(CDATE, "%Y%m") as month, 
COUNT(*) as freq, SUM(BILLS/count(*)) as amount FROM PROCESSED 
where CDATE>= DATE(NOW() - INTERVAL 6 MONTH) GROUP BY CUST_ID, month  having count(*) >=3 
order by cust_id, month) T where CUST_ID != 2750 and CUST_ID != 1 group by CUST_ID

我了解 group by 子句不允许非聚合列，我需要月份作为列吗？

Answer 1

SELECT CUST_ID, AVG(freq), AVG(amount),MONTH from 
(SELECT CUST_ID, DATE_FORMAT(CDATE, "%Y%m") as month, 
COUNT(*) as freq, SUM(BILLS)/count(*) as amount FROM PROCESSED 
where CDATE>= DATE(NOW() - INTERVAL 6 MONTH) GROUP BY CUST_ID, MONTH  having count(*) >=3 
order by cust_id, month) T where CUST_ID != 2750 and CUST_ID != 1 group by CUST_ID,MONTH

我得到了解决方案 - 问题是分组依据没有月份列，因此月份列不知道在聚合结果中附加到哪一行