Mysql2::Error: Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Question

I wrote this query in rails 5 application with 2.4.0 ruby I have a problem with my query and mysql throws the following error:

Mysql2::Error: Expression #5 of SELECT list is not in GROUP BY clause and
contains nonaggregated column 'order_it.hidden_companies' which is not
functionally dependent on columns in GROUP BY clause; this is incompatible
with sql_mode=only_full_group_by: SELECT orders.code as Order_code, 
customers.name as Company_name.... the reste of my query

Query is:

SELECT
    orders.code AS Order_code,
    customers.name AS Company_name,
    contacts.name AS Name,
    order_it.code AS Medium_Code,
    order_it.hidden_companies,
    GROUP_CONCAT(case when contact_resource_types.code = 'mainphone' then
        contact_resources.value end SEPARATOR '|') AS Main_phone,
    GROUP_CONCAT(case when contact_resource_types.code = 'mainfax' then 
        contact_resources.value end SEPARATOR '|') AS Main_fax,
    GROUP_CONCAT(case when contact_resource_types.code = 'mainemail' then 
        contact_resources.value end SEPARATOR '|') AS Main_email,
    GROUP_CONCAT(case when contact_resource_types.code = 'mainsite' then
        contact_resources.value end SEPARATOR '|') AS Main_site,
    GROUP_CONCAT(case when contact_resource_types.code = 'procell' then
        contact_resources.value end SEPARATOR '|') AS Pro_cell,
    GROUP_CONCAT(case when contact_resource_types.code = 'cell' then    
        contact_resources.value end SEPARATOR '|') AS Cell,
    contact_postal_addresses.street_1 AS Street_1,
    cities.name AS City,
    regions.name AS Region
FROM 
    contacts
    INNER JOIN contact_postal_addresses 
        ON contact_postal_addresses.contact_id =  contacts.id
    INNER JOIN cities 
        ON cities.id = contact_postal_addresses.city_id
    INNER JOIN regions 
        ON regions.id = cities.region_id
    INNER JOIN orders
        ON order_id = orders.id
    INNER JOIN contacts AS customers 
        ON customers.id = orders.customer_contact_id
    INNER JOIN 
    (SELECT DISTINCT
        order_items.order_id,  
        order_items.hidden_companies, media.code 
    FROM 
        order_items 
        INNER JOIN media
            ON order_items.medium_id = media.id 
    WHERE 
        media.id = 76
    ) AS order_it ON order_it.order_id = orders.id
    LEFT OUTER JOIN contact_resources 
        ON contact_resources.contact_id = contacts.id
    INNER JOIN contact_resource_types 
        ON contact_resources.contact_resource_type_id = contact_resource_types.id
WHERE
    contacts.listing = 1
    and orders.country_id = 5
    and orders.order_status_id = 1
    and contact_resource_types.code in   
      ('mainphone','mainfax','mainemail','mainsite','procell','cell')
GROUP BY
    contacts.id
ORDER BY
    contacts.updated_at ;

score 0 · Accepted Answer · answered Jul 09 '17 at 11:07

You should GROUP BY using all columns in your SELECT and ORDER BY that are non-aggregated, to be on the safe side. [Having a full definition of all involved tables might help simplify the query].

That is:

GROUP BY
     contacts.id
    ,Order_code
    ,Company_name
    ,Name
    ,Medium_Code
    ,hidden_companies
    ,Street_1
    ,City
    ,Region
    ,contacts.updated_at      
ORDER BY
    contacts.updated_at

Mysql2::Error: Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column

1 Answers1