SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated

Asked Mar 20 '22 at 18:45

Active Mar 20 '22 at 18:51

Viewed 65 times

I got this error when I used GROUP BY in the query below

Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'table_name.total_ttc' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in ............

function situationsList(){ 
    $query = "SELECT `ref_s`, `total_ttc`, `created_at`, `mode_pay` FROM `situations` GROUP BY ref_s ";
    $statement = $GLOBALS['PDO']->prepare($query);
    $statement->execute();

    $data = array();
    while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
        $data[] = $row;
    }
    return json_encode($data);
}

The query works just fine in localhost but when I upload it on server I get that error!

edited Mar 20 '22 at 18:51

ADyson

57,178
14
51
63

asked Mar 20 '22 at 18:45

Richard Branson

Because your server has stricter settings – ADyson Mar 20 '22 at 18:50
1

The query doesn't make much sense, why have you got a group by without any kind of aggregate function? – ADyson Mar 20 '22 at 18:51
have you looked in to the manual https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html – nbk Mar 20 '22 at 18:53
I have to use the group by ref_s because one SITUATION have a reference number and can have one or multiple ITEMs. I want get a list of SITUATIONS and I dont want to list them with the same reference number – Richard Branson Mar 20 '22 at 18:54
Thank you! I solved the issue by removing ONLY_FULL_GROUP_BY – Richard Branson Mar 20 '22 at 19:20
It would be better to make the query compliant with modern standards instead – ADyson Mar 20 '22 at 20:05

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated

0 Answers0