问题描述:

when i got upgraded my ubuntu from 15.10 to 16.04 i have this erro in my yii2 project

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3

of SELECT list is not in GROUP BY clause and contains nonaggregated column

'iicityYii.opportunity_conditions.money' which is not functionally dependent

on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The SQL being executed was:

SELECT SUM(oc.money),op.id,oc.money,

op.mantaghe,

op.`time`, op.`id`, `op`.`logo`,

`pd`.`user_id`, `op`.`name`,

`pd`.`co_name`, `op`.`address`,

`op`.`project_type_id`, `op`.`state_id`

FROM `opportunity` op

INNER JOIN `profile_details` pd ON op.user_id=pd.user_id

INNER JOIN `opportunity_conditions` oc ON op.id=oc.opportunity_id

GROUP BY `op`.`id`

ORDER BY `op`.`id` DESC

how to solve my problem ?

网友答案:

In you select you have an aggregate function sum and a set of column name, the error tell you that you have not specified the correct list of column name in group by clause . could be you should add more columns name in group by probably related to the profile_details, opportunity_conditions table

You have also ,(opportunity.id),(opportunity_conditions.money), (opportunity.mantaghe), why the () if you need sum you must add sum to all column

sum(opportunity.id), sum(opportunity_conditions.money),

sum(opportunity.mantaghe),

otherwise if thes are normal columns you should use the normal sintax without ()

opportunity.id, opportunity_conditions.money,opportunity.mantaghe,

I have tried to rewrite a possible query

 SELECT SUM(opportunity_conditions.money),
        `opportunity`.`id`,
        `opportunity_conditions.money`,
        `opportunity.mantaghe`, 
        `opportunity`.`time`, 
        `opportunity`.`logo`, 
        `profile_details`.`user_id`,
        `opportunity`.`name`, 
        `profile_details`.`co_name`,
        `opportunity`.`address`, 
        `opportunity`.`project_type_id`,
        `opportunity`.`state_id` 
FROM `opportunity` 
INNER JOIN `profile_details` ON `opportunity`.`user_id`= `profile_details`.`user_id` 7
INNER JOIN `opportunity_conditions` ON `opportunity`.`id`=`opportunity_conditions`.`opportunity_id` 
GROUP BY`opportunity`.`id`,   `profile_details`.`user_id`,`opportunity_conditions.money`,  
ORDER BY `opportunity`.`id` DESC

with group by on the essential column name (i hope)

GROUP BY`opportunity`.`id`,   `profile_details`.`user_id`,`opportunity_conditions.money`,  
网友答案:

OR Just Run the

$ sudo mysql -u root -p

change the SQL Mode for Your MySQL Server Instance

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
相关阅读:
Top