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
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!