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.

Answer 1

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

Answer 2

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