0

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

shashikant pandit
  • 476
  • 6
  • 8
  • Possible duplicate of [I am getting an error in MySql related to only\_full\_group\_by when executing a query](http://stackoverflow.com/questions/34115174/i-am-getting-an-error-in-mysql-related-to-only-full-group-by-when-executing-a-qu) – Mat Oct 17 '16 at 13:45
  • if you only group by `u.u_company` you must use an aggregation-function for every other column in the select statement. If you want to group by the other columns as well, then you need to put all the other columns in the group by condition as well. This might help you: http://www.w3schools.com/sql/sql_groupby.asp – Nebi Oct 17 '16 at 13:45
  • this time i am using u.u_company,aggregate_function( u.u_company) but FUNCTION pharmezy-2.aggregate_function does not exist – shashikant pandit Oct 17 '16 at 14:19
  • An Aggregate-Function for example would be `MAX()`, `MIN()`, `AVG()`, `COUNT()` etc. there are examples in the link provided. – Nebi Oct 17 '16 at 15:58

1 Answers1

0

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!

Nebi
  • 306
  • 2
  • 10