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

Question

I have wrote some query that is supported in my localhost but when i uploaded into my server it shows some error in my query.

My query

 $query= $this->db->query("SELECT i.`Repay_id`, 
  COALESCE(SUM(`Amount`) ,0)    AS Amount
    ,p.`RLAmt` AS RLAmt,
    s.`TAmt` as TAmt,
    r.`PName` as TName,
    s.`TMode` as TMode,
    s.`TDate` as TDate,
    s.`TDAmt` as TDAmt,
    r.`Phone` as Phone,
    s.`TAcNo` as TAcNo

FROM  repay_schedule i
LEFT  JOIN (
   SELECT TName, TAmt,TAcNo,TMode,TDate,TDAmt
   FROM   tipup_payment  
    ) s ON s.`TAcNo` = i.`Repay_id`
LEFT  JOIN (
   SELECT Phone,PName,Area,pcode
   FROM   parmaster
    ) r ON r.`pcode` = s.`TName`
LEFT  JOIN (
SELECT TRAcNo, COALESCE(SUM(`RLAmt`) ,0) AS RLAmt,TRBAmt FROM   tipup_receipt GROUP  BY TRAcNo
     ORDER BY TRid DESC
   ) p ON p.`TRAcNo` = s.`TAcNo`
    WHERE  `Date` <='$newDate2' 
   GROUP BY Repay_id");

My error

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Oct 02 '19 at 07:16

score 0 · Answer 1 · answered Oct 02 '19 at 07:21

0

You may try this if you have enough privileges

set global sql_mode = 'NO_ENGINE_SUBSTITUTION';

but I would not consider this a good solution.

answered Oct 02 '19 at 07:21

giubueno

58
8

where i set this – Jacky Oct 02 '19 at 07:22
@Jack turning off the setting is **not** the right solution! The query may not generate this error message, but the results may not be what you expect. – Shadow Oct 02 '19 at 07:36
@jack, as I said "but I would not consider this a good solution." – giubueno Oct 03 '19 at 08:40

score 0 · Answer 2 · answered Oct 02 '19 at 07:28

0

The error occurs because you do an aggregation to the 'Amount' (SUM(Amount)) and not on the other fields you use.

When you have the mode 'only_full_group_by' activated and have at least one aggregation, you need to do an aggregegation to all fields that are not included in the 'group by' clause.

You can either do an aggregation like min(), max(), sum(), ... to all other fields you use or deactivate the mode on your MySql server.

answered Oct 02 '19 at 07:28

kaputnix

123
10

how to deactive the mode on server – Jacky Oct 02 '19 at 07:31
You can deactivate it by using the mysql console: `SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));`. If you want to disable it permanently, search in your MySql config e.G. '/etc/mysql/my.cnf' for the line `sql_mode = "...,only_full_group_by,..."` and remove this mode. then restart the MySql service. – kaputnix Oct 02 '19 at 07:33
it display as access denied – Jacky Oct 02 '19 at 07:36
@Jack turning off the setting is **not** the right solution! The query may not generate this error message, but the results may not be what you expect. – Shadow Oct 02 '19 at 07:42
okey shadow then what is the solution for this? – Jacky Oct 02 '19 at 07:43

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

2 Answers2