我在执行查询时遇到与sql_mode = only_full_group_by相关的wampserver MySql 5.7.14中的错误

Question

I have update my wampserver (X64)3.0.6 and install Mysql 5.7.14 with php for a web application I am working on. when run in older versions of MySql 5.6.17 it works fine. But Since upgrading to 5.7.14 I get this error:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'pharmezy-2.p.idproduct' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

And My Query Is :

    SELECT p.idproduct, p.idproduct, p.product_name, pi.image_url, u.u_company, 
    p.product_rating FROM product p join product_image pi on p.idproduct=pi.idproduct 
    join users u on p.idusers = u.idusers where p.idprocess=1 and p.product_status=1
    group by u.u_company

Thanku

Answer 1

Because you seem to have trouble with that I write this answer.

You need to use either Aggregate-Functions for the columns you want to aggregate like MAX() or COUNT(). Or you write down all the columns you want to group by. Like you did with u.u_company. This can also be done with a SELECT DISTINCT.

For example using Aggregate-Function MAX() and only grouping by u.u_company:

SELECT  MAX(p.idproduct) AS MAX_p_idproduct ,
        MAX(p.product_name) AS MAX_p_product_name ,
        MAX(pi.image_url) AS MAX_pi_image_url ,
        u.u_company ,
        MAX(p.product_rating) AS MAX_p_product_rating
FROM    product p
        JOIN product_image pi ON p.idproduct = pi.idproduct
        JOIN users u ON p.idusers = u.idusers
WHERE   p.idprocess = 1
        AND p.product_status = 1
GROUP BY u.u_company

Not using Aggregate-Function, but grouping by all columns:

SELECT  p.idproduct ,
        p.product_name ,
        pi.image_url ,
        u.u_company ,
        p.product_rating
FROM    product p
        JOIN product_image pi ON p.idproduct = pi.idproduct
        JOIN users u ON p.idusers = u.idusers
WHERE   p.idprocess = 1
        AND p.product_status = 1
GROUP BY u.u_company ,
        p.idproduct ,
        p.product_name ,
        pi.image_url ,
        u.u_company ,
        p.product_rating    

Same but different to the last Query using DISTINCT instead of GROUP BY:

SELECT  DISTINCT p.idproduct ,
        p.product_name ,
        pi.image_url ,
        u.u_company ,
        p.product_rating
FROM    product p
        JOIN product_image pi ON p.idproduct = pi.idproduct
        JOIN users u ON p.idusers = u.idusers
WHERE   p.idprocess = 1
        AND p.product_status = 1

Also possible to use Aggregation-Funtion with it (Same as first Query):

SELECT  DISTINCT MAX(p.idproduct) AS MAX_p_idproduct ,
        MAX(p.product_name) AS MAX_p_product_name ,
        MAX(pi.image_url) AS MAX_pi_image_url ,
        u.u_company ,
        MAX(p.product_rating) AS MAX_p_product_rating
FROM    product p
        JOIN product_image pi ON p.idproduct = pi.idproduct
        JOIN users u ON p.idusers = u.idusers
WHERE   p.idprocess = 1
        AND p.product_status = 1

But in all cases you need to know what you to do. Do you only want to group by u.u_company or do you want to group by more columns. But either way all columns are involved in grouping. They need to be in the group by or aggregated by a Aggregate-Funtion!