Alko Alko - 3 months ago 15
MySQL Question

MySql, SELECT list is not in GROUP BY clause and contains nonaggregated column

I'm using MySql 5.7, and I'm getting an error in this query:

SELECT
COUNT(id) AS sales,
SUM(amount) AS total,
created
FROM
`payments`
WHERE status =1
GROUP BY MONTH(created);


if I change
GROUP BY MONTH(created)

with:

GROUP BY created


then the error is gone. I dont have access to my.ini, to make changes to sql_mode.

Answer

Since you group month-wise, you shold output the months also

  SELECT COUNT(id) AS sales,
         SUM(amount) AS total,
         MONTH(created)
  FROM payments 
  WHERE status = 1
  GROUP BY MONTH(created)

Otherwise MySQL has to pick a created value from the group. But you should define what to display.

Another possibility instead of outputting MONTH(created) would be a aggreagtion of the date like min(created) which would output the earliest date of each month.

Comments