New version of php/mysql got an aggregate error in GROUP BY clause

Question

I've got an error on my code saying "Invalid in the select list because it is not contained/ aggregate in the group by clause" like that. This error seems familiar to me in "MS SQL Server". I haven't encountered this error before in MySQL. This error came up when I upgraded my Php version to 7. All my previous web-based program before were affected

I tried to add more column in my "group by" clause, the error got away but the output is not what I am expected. The code below is my old code.

SELECT  SUM(s.pscore) as towtal, s.pscore AS totalScore, s.cri_id, 
c.can_id, c.canid,c.can_name FROM score s INNER JOIN candidate c ON 
s.can_id = c.can_id WHERE cat_id=1 AND s.cri_id = '".$rows['cri_id']."' 
AND c.can_sex = 'Female' AND c.can_id='".$kert[$i]."'
 GROUP BY s.can_id ORDER BY s.can_id ASC LIMIT 5

When I add GROUP BY s.can_id, s.pscore there will be no errors, but the output is not what I am expected

Possible duplicate of [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](https://stackoverflow.com/q/13999817/1843510) — zbee, Jul 29 '19 at 03:05
You're open to SQL injections, use [prepared statements](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Jaquarh, Jul 29 '19 at 03:07

score 0 · Accepted Answer · answered Jul 29 '19 at 05:20

0

go to your database and in sql run this command

set GLOBAL sql_mode='';

answered Jul 29 '19 at 05:20

PHP Geek

3,949
1
16
32

Thanks! Can you tell me why do we need this? And why is MySQL is having that kind of error now. Because in older version it doesnt have that kind of error. – Bug Report Aug 01 '19 at 01:40

score 0 · Answer 2 · answered Jul 29 '19 at 05:23

0

SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

answered Jul 29 '19 at 05:23

Mehul Koradiya

129
6

score 0 · Answer 3 · answered Jul 29 '19 at 07:09

An example of a valid query:

SELECT  SUM(s.pscore) as towtal
     , s.pscore AS totalScore
     , s.cri_id
     , c.can_id
     , c.canid
     , c.can_name 
  FROM score s
  JOIN candidate c 
   ON s.can_id = c.can_id 
 WHERE cat_id = 1 
   AND s.cri_id = '".$rows['cri_id']."' 
   AND c.can_sex = 'Female' 
   AND c.can_id = '".$kert[$i]."'
 GROUP 
    BY s.pscore 
     , s.cri_id
     , c.can_id
     , c.canid
     , c.can_name 
 ORDER 
    BY s.can_id ASC 
 LIMIT 5;

Note that this query is vulnerable to injection

New version of php/mysql got an aggregate error in GROUP BY clause

3 Answers3