CODEWITHSUNDEEP
mysqlsql-order-by
Arun
Arun

Reputation: 3721

mysql error "ERROR 3029 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to the result of a non-aggregated query"

I am executing the below query

SELECT DISTINCT n.nid AS entity_id
FROM node n
INNER JOIN og_membership om ON n.nid=om.etid AND om.entity_type='node'
INNER JOIN foster_animal_capacity fc ON n.nid=fc.person_id
LEFT OUTER JOIN field_data_field_attributes fa ON n.nid=fa.entity_id
LEFT OUTER JOIN foster_person_black_outs fb ON n.nid=fb.person_id
WHERE -- n.nid = 1441663 AND 
(om.gid = 464) AND (fc.animal_type_id = 3) 
AND ((fc.capacity - fc.occupied)>=1) 
AND ((fb.start_date IS NULL) OR (fb.end_date < 1523577600) OR (fb.start_date > 1522540800))
AND ((SELECT pid FROM `animal_history` WHERE `TYPE` = 'Foster Return'   
AND pid = n.nid ORDER BY DATE_FORMAT(FROM_UNIXTIME( UNIX_TIMESTAMP( ) - MAX( CAST(`time` AS UNSIGNED) ) ) ,'%e')));

The query executing perfectly in MySql 5.5.5

But showing the below error in MySql 5.7.21 

ERROR 3029 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to the result of a non-aggregated query

Why is this happening? And how can I overcome this issue?

I am not able to find any documentation for the error code 3029

Upvotes: 4

Views: 6385

Answers (1)

Pankaj Kumar
Pankaj Kumar

Reputation: 570

As mentioned in MySQL documention : MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6

For detail please check: MySQL Handling of GROUP BY

Upvotes: 3

Related Questions