Error 1055 SQL - SELECT list is not in GROUP BY

Question

I'm struggling with this SQL consult, the error message is:

1055 - Expression #10 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ctrl2019.s.cgsc_cuenta' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by.

And here is the code:

SELECT c.cgcu_cuenta AS id, g.ger_cuenta, g.ger_nombre, p.cgp_cuenta, p.cgp_nombre, r.rub_cuenta, r.rub_nombre, c.cgcu_cuenta, c.cgcu_nombre, s.cgsc_cuenta, s.cgsc_nombre, SUM(IFNULL(a.debe, 0)) - SUM(IFNULL(a.haber, 0)) AS debe, 0 AS haber, 'D' AS nat_id, c.cgcu_cuenta AS cuenta, c.cgcu_nombre AS nombre FROM ctrl2019.cat_Genero g INNER JOIN ctrl2019.cat_CgGrupo p USING(ger_id) INNER JOIN ctrl2019.cat_Rubro r USING(ger_id, cgp_id) INNER JOIN ctrl2019.cat_CgCuenta c USING(ger_id, cgp_id, rub_id) INNER JOIN ctrl2019.cat_CgSubcuenta s USING(ger_id, cgp_id, rub_id, cgcu_id) LEFT JOIN ( SELECT a.ger_id, a.grp_id, a.rub_id, a.cta_id, a.sct_id, SUM(IFNULL(a.msl_debe, 0)) AS debe, SUM(IFNULL(a.msl_haber, 0)) AS haber FROM ldf.vin_EntePublicoLDF e INNER JOIN ldf.blz_Mensual_2019 a USING(gpo_id, ur_id) WHERE e.ejr_id = 2019 AND a.ger_id = 1 AND e.ent_id = 12 AND a.msc_id IN (0, 1, 2, 3) GROUP BY a.ger_id, a.grp_id, a.rub_id, a.cta_id, a.sct_id )a ON s.ger_id = a.ger_id AND s.cgp_id = a.grp_id AND s.rub_id = a.rub_id AND s.cgcu_id = a.cta_id AND s.cgsc_id = a.sct_id WHERE g.ger_id = 1 GROUP BY g.ger_id, p.cgp_id, r.rub_id, c.cgcu_id;

I have no idea why it gives me this error, i'm new in sql.

The error seems quite clear. You have unaggregated columns in the SELECT that are not in the GROUP BY, such as c.cgcu_cuenta. — Gordon Linoff
– Gordon Linoff, Commented Feb 20, 2020 at 18:12

Arif Ali · Accepted Answer · 2020-02-20 18:18:13Z

1

you just need to add all the columns in GROUP BY which are present in the SELECT statement.

answered Feb 20, 2020 at 18:18

Arif Ali

5112 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SS_DBA Over a year ago

Back your statement up with a solution for OP.

JUAN GB Over a year ago

I already did it, but then i have different message: 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

digital.aaron · Accepted Answer · 2020-02-20 18:33:02Z

Seems like simple aggregation here. I cleaned up the code and added some columns to the GROUP BY.

SELECT id = c.cgcu_cuenta ,g.ger_cuenta ,g.ger_nombre ,p.cgp_cuenta ,p.cgp_nombre ,r.rub_cuenta ,r.rub_nombre ,c.cgcu_cuenta ,c.cgcu_nombre ,s.cgsc_cuenta ,s.cgsc_nombre ,debe = SUM(IFNULL(a.debe, 0)) - SUM(IFNULL(a.haber, 0)) ,haber = 0 ,nat_id = 'D' ,cuenta = c.cgcu_cuenta ,nombre = c.cgcu_nombre FROM ctrl2019.cat_Genero g INNER JOIN ctrl2019.cat_CgGrupo p ON USING(ger_id) INNER JOIN ctrl2019.cat_Rubro r ON USING(ger_id, cgp_id) INNER JOIN ctrl2019.cat_CgCuenta c USING(ger_id, cgp_id, rub_id) INNER JOIN ctrl2019.cat_CgSubcuenta s USING(ger_id, cgp_id, rub_id, cgcu_id) LEFT JOIN ( SELECT a2.ger_id ,a2.grp_id ,a2.rub_id ,a2.cta_id ,a2.sct_id ,debe = SUM(IFNULL(a2.msl_debe, 0)) ,haber = SUM(IFNULL(a2.msl_haber, 0)) FROM ldf.vin_EntePublicoLDF E INNER JOIN ldf.blz_Mensual_2019 a2 USING(gpo_id, ur_id) WHERE e.ejr_id = 2019 AND a2.ger_id = 1 AND e.ent_id = 12 AND a2.msc_id IN (0, 1, 2, 3) GROUP BY a2.ger_id ,a2.grp_id ,a2.rub_id ,a2.cta_id ,a2.sct_id ) A ON s.ger_id = A.ger_id AND s.cgp_id = A.grp_id AND s.rub_id = A.rub_id AND s.cgcu_id = A.cta_id AND s.cgsc_id = A.sct_id WHERE g.ger_id = 1 GROUP BY g.ger_id ,p.cgp_id ,r.rub_id ,C.cgcu_id ,c.cgcu_cuenta ,g.ger_cuenta ,g.ger_nombre ,p.cgp_cuenta ,p.cgp_nombre ,r.rub_cuenta ,r.rub_nombre ,c.cgcu_nombre ,s.cgsc_cuenta ,s.cgsc_nombre

I already did it, but then i have different message: 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Your query doesn't have an ORDER BY clause in it, so I don't know how you are getting that error. In order to further debug you issue, you'd need to update your question with a description of what you're trying to accomplish, your table structures, sample data, and expected results.

Collectives™ on Stack Overflow

Error 1055 SQL - SELECT list is not in GROUP BY

2 Answers 2

2 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Related