mysql query error SELECT list is not in GROUP BY clause and contains nonaggregated column

Question

i am using pentaho reporting and i have a query in my report, my query work on my local computer but when i try to deploy my application to server i get this error..

i am using mysql 5.6

    28-Nov-2016 09:47:38.490 INFO [localhost-startStop-1] org.apache.catalina.startup.HostConfig.deployWAR Deployment of web application archive /root/apache-tomcat-8.5.5/webapps/ROOT.war has finished in 44,957 ms
28-Nov-2016 09:47:38.496 INFO [main] org.apache.coyote.AbstractProtocol.start Starting ProtocolHandler [http-nio-80]
28-Nov-2016 09:47:38.502 INFO [main] org.apache.coyote.AbstractProtocol.start Starting ProtocolHandler [ajp-nio-8009]
28-Nov-2016 09:47:38.504 INFO [main] org.apache.catalina.startup.Catalina.start Server startup in 45038 ms
ERROR org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor - 48154607: Report processing failed.
org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: Select id, IFNULL(SUM(debet) - SUM(kredit),0) as saldoawal2 from (

SELECT
    g.id,
     g.code,
    a.trx_date,
    IF(a.trx_type = 1 OR a.trx_type = 2, a.relation,IF(a.trx_type = 3,e.name,IF(a.trx_type = 4,f.name, b.amount))) as relasi,
     a.trx_no,
     b.dt,
    IF(b.dt = 'D', b.amount,  0) as 'debet',
    IF(b.dt = 'K', b.amount,  0) as 'kredit'
FROM transaksi_kas_detail b     
     INNER JOIN transaksi_kas a  ON a.id = b.transaksi_kas_id
     LEFT OUTER JOIN customer e ON a.customer_id = e.id
     LEFT OUTER JOIN supplier f ON a.supplier_id = f.id
     LEFT OUTER JOIN invoice c ON b.sales_invoice_id = c.id
     LEFT OUTER JOIN purchase_invoice d ON b.purchase_invoice_id = d.id  
     LEFT OUTER JOIN account g ON b.account_id = g.id
WHERE g.id != '' 

UNION 

select 
    d.id,
    d.code,
    a.invoice_date as trx_date,
    b.name as relasi,
    a.invoice_no as trx_no,
    'D' as dt,
    a.total_amount as debet,
    0 as kredit
FROM
    invoice a
    LEFT JOIN customer b ON  a.customer_id = b.id
    LEFT JOIN group_customer c ON c .id = b.group_customer_id   
    LEFT JOIN account d ON d.id = c.piutang_dagang_id

union

select 
    d.id,
    d.code,
    a.purchase_date as trx_date,
    b.name as relasi,
    a.purchase_no as trx_no,
    'D' as dt,
    0 as debet,
    a.total_amount as kredit
FROM
    purchase_invoice a
    LEFT JOIN supplier b ON  a.supplier_id = b.id
    LEFT JOIN group_supplier c ON c .id = b.group_supplier_id   
    LEFT JOIN account d ON d.id = c.hutang_dagang_id

union

select 
    d.id,
    d.code,
    a.invoice_date as trx_date,
    b.name as relasi,
    a.invoice_no as trx_no,
    'D' as dt,
    a.total_amount as debet,
    0 as kredit
from
    invoice a
    LEFT JOIN customer b ON a.customer_id = b.id
    LEFT JOIN group_customer c ON b.group_customer_id = c.id
    LEFT JOIN account d ON c.uang_muka_id = d.id
    GROUP BY a.invoice_no
union

select 
    d.id,
    d.code,
    a.purchase_date as trx_date,
    b.name as relasi,
    a.purchase_no as trx_no,
    'D' as dt,
    0 as debet,
    a.total_amount as kredit
from
    purchase_invoice a
    LEFT JOIN supplier b ON a.supplier_id = b.id
    LEFT JOIN group_supplier c ON b.group_supplier_id = c.id
    LEFT JOIN account d ON c.uang_muka_id = d.id
    GROUP BY a.purchase_no
) ffasdff 
WHERE id =${acid} AND trx_date < ${dateFrom}
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:210) ~[pentaho-reporting-engine-classic-core-5.1.0.0-752.jar:5.1.0.0-752.-1]
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:162) ~[pentaho-reporting-engine-classic-core-5.1.0.0-752.jar:5.1.0.0-752.-1]

and this one.

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'accounter.d.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

see this line SELECT list is not in GROUP BY clause and contains nonaggregated column 'accounter.d.id'

i still have no idea what this mean.

what must i do, i am using Fedora 24 x64.

Basically, if a non-aggregated column appears in the SELECT, then it should also appear in the GROUP BY. An aggregated column is an argument of a function like MAX,MIN,COUNT,SUM, AVG, GROUP_CONCAT, etc. An non-aggregated column isn't. — Strawberry, Nov 28 '16 at 10:18
You have to disable SQL_MODE = only_full_group_by, but please consider that this makes MySQL less comply to SQL standard. Please see this [link](http://stackoverflow.com/questions/23921117/disable-only-full-group-by). Perhaps that is what you need. — Hermanto, Nov 28 '16 at 10:19
@akiong, It can be yes or no, depending on your needs. But I suggest NO. Please read another answer on the post that may give some idea to you. — Hermanto, Nov 28 '16 at 10:23
Incidentally, I cannot see any aggregationg functions in your query — Strawberry, Nov 28 '16 at 10:24
@akiong, But please beware that this makes MySQL less comply to SQL standard. SQL standard stated that select list for non-aggregated column must be appear on GROUP BY clause. — Hermanto, Nov 28 '16 at 10:27
@Hermanto i tried to run `SET sql_mode = ''`, but still get the same error.. — , Nov 28 '16 at 10:28
Then you have to make concern to what @Strawberry has mentioned. — Hermanto, Nov 28 '16 at 10:29
@Strawberry actually i do not know which `accounter.d.id` because theres more than 1 accounter d.id — , Nov 28 '16 at 10:30
@Strawberry hey...you are right...theres no `accounter`, sorry before,.. `accounter` is my database's name — , Nov 28 '16 at 10:35
OK. Just get rid of all the GROUP BY clauses. If you want DISTINCT results, use DISTINCT — Strawberry, Nov 28 '16 at 10:37
@Strawberry i am deleting all `GROUP BY` and change `SELECT` to `SELECT DISTINCT` and finally this way works for me.. — , Nov 28 '16 at 12:05
@Strawberry Thank you.. .Btw i still do not know why i cannot using `GROUP BY` — , Nov 28 '16 at 12:05

score -1 · Answer 1 · answered Nov 28 '16 at 10:43

-1

You are creating a virtual table using sub query where you are replacing purchase_no with trx_no due to which it was not being found in group by clause. You have to use trx_no in group by clause.

answered Nov 28 '16 at 10:43

Faizan Younus

793
1
8
13

my query run on my local database / mysql – Nov 28 '16 at 10:50

mysql query error SELECT list is not in GROUP BY clause and contains nonaggregated column

1 Answers1