0

This is my query:

SELECT u_id, created_date,
COUNT(u_id) AS count_total
FROM table
WHERE statement='this'
GROUP BY DAY(created_date)
ORDER BY created_date ASC

The error that I get is:

Query error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column.

I've read this post but I don't know how to get this working, as now the problem seems to be the addition of DAY.

I've tried changing the SELECT statement to:

SELECT u_id, ANY_VALUE(created_date) as test_date

but that didn't work.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
eskimo
  • 2,421
  • 5
  • 45
  • 81

2 Answers2

0

mysql 5.7 changed the default way group by works. (I'm assuming you are on 5.7 for this answer.)

Basically, because there are multiple possible values for u_id, mysql doesn't know which one you would like. As of 5.7 with default settings, it will throw the error you are seeing. The answer you referenced, has a nice diagram, check it out again. One way to get your query to return results is:

SELECT ANY_VALUE(u_id), ANY_VALUE(created_date),
COUNT(u_id) AS count_total
FROM table
WHERE statement='this'
GROUP BY DAY(created_date)
ORDER BY created_date ASC

The mysql manual page has a good explanation as well https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

Tim Mickey
  • 361
  • 3
  • 10
  • I thought something like this should be solution, however I still get `Query error: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'database.table.created_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` – eskimo Jan 23 '18 at 11:07
  • Never mind, I misread the error. Changing the `ORDER BY` clause to `ORDER BY DAY(created_date)` fixed that error. But by changing it all date values are 'Jan 1'. – eskimo Jan 23 '18 at 11:16
  • In the result array, the label for created date was literally `ANY_VALUE(created_date)` so changed the `SELECT` to `ANY_VALUE(created_date) as created_date` – eskimo Jan 23 '18 at 11:38
0

Just make sure, your field created_date not contain null or blank space. So, the DAY function can run correnctly. check your field by this statement and see if there is any null or blank space in result:

SELECT DISTINCT created_date FROM table;
Ondi
  • 51
  • 4