CODEWITHSUNDEEP
phpmysql
Darkwind
Darkwind

Reputation: 345

How to change Mysql query (GROUP BY) Error with sql_mode=only_full

I've big MySql table with this columns:

id  int(10) unsigned Auto Increment  
pr_id   int(10) unsigned     
temp_air    varchar(255) NULL        
created_at  timestamp NULL   
updated_at  timestamp NULL

I want get rows where data is 24 hours ago and get from this selection data for every hour.

Query I tried is:

SELECT *
FROM `statistics`
WHERE DATE(created_at) = CURDATE()- INTERVAL 1 DAY
GROUP BY  
EXTRACT(HOUR FROM created_at)
ORDER BY
EXTRACT(HOUR FROM created_at)

And the error occurs:

Error in query (1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'statistic.statistics.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

How must I change my query? Where is my mistake?

Upvotes: 0

Views: 860

Answers (1)

axiac
axiac

Reputation: 72256

SELECT * is incompatible with GROUP BY. Before version 5.7.5, MySQL used to accept invalid GROUP BY queries but it returned indeterminate values.

A GROUP BY query doesn't return rows from the database; it generates on-the-fly the records it returns. From each group of rows produced according to the GROUP BY clause it generates one row. SELECT * doesn't make sense with GROUP BY; if one group contains more than one row, what value should it return for the id column, f.e.?

If you don't want to produce one row for each hour then GROUP BY is not what you need.

But if you want to generate one row for each hour then the query should be something like:

SELECT EXTRACT(HOUR FROM created_at), AVG(temp_air) AS temp_air
FROM `statistics`
WHERE created_at >= DATE_SUB(CURTIME(), INTERVAL 1 DAY)
GROUP BY EXTRACT(HOUR FROM created_at)
ORDER BY EXTRACT(HOUR FROM created_at)

The DATE() function used by your original WHERE condition (DATE(created_at) = CURDATE()- INTERVAL 1 DAY) doesn't allow MySQL to use an index to filter the rows to process and it needs to read the entire table data. It can use an index now (assuming there is an index on the create_at column).

Upvotes: 1

Related Questions