0
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

I understand the group by clause does not allow non-agg column, I need the month as a column?

Raghu
  • 313
  • 2
  • 7
  • 19
  • 1
    You need `month` in your second GROUP BY, just exactly like the error message tells you. – Ken White Mar 21 '18 at 01:22
  • 1
    Possible duplicate of [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e) – Ken White Mar 21 '18 at 01:23

1 Answers1

0
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

I got the solution - The Issue was group by did not have the month column and so the month column does not know which row to be attached with in the aggregated results

Raghu
  • 313
  • 2
  • 7
  • 19