Error 42803. column XXXX must showing clause GROUP BY or be use in an aggregation function

Question

Trying to make an over (order by Id asc rows between unbounded preceding and current row) in a Sum, I try to get an accumulated sum for each value. As far as I got:

    SELECT  c.id AS Id, c.centro AS Description,  g.id as GroupId, cuen.id as BillId, r.mes_actualizado as UpdatedMonth, cri.agno_mes as YearMonth, gc.signo as Sign, 
    cuen.descripcion->>:language as Bill, false as Comparable, true as Divisor, 
    SUM(cri.importe_cal * gc.signo) over (order by c.Id asc rows between unbounded preceding and current row) AS Amount , 
    g.descripcion->> :language as Group, g.es_total as IsTotal,
    cri.importe, cri.importe_cal  
    FROM public.tabla1 m 
    INNER JOIN public.tabla2 mg ON(mg.ef_modelo_id = m.id) 
    INNER JOIN public.tabla3 g ON(g.id = mg.ef_grupo_id) 
    INNER JOIN public.tabla4 gc ON(gc.ef_grupo_id = g.id) 
    INNER JOIN public.tabla5 cuen ON(cuen.id = gc.ef_cuenta_id) 
    INNER JOIN public.tabla6 cri ON(cri.ef_cuenta_id = cuen.id) 
    INNER JOIN public.tabla7 r ON(r.id = cri.ef_real_id) 
    INNER JOIN public.tabla8 c ON(c.id = r.centro_id )  
    INNER JOIN public.tabla9 mr ON(m.id = mr.ef_modelo_id) 
    INNER JOIN public.tabla10  u ON(mr.rol_id = u.tpl_rol_id) 
    INNER JOIN public.tabla11 cu ON(cu."userId" = u."pId") AND(cu.centro_id = r.centro_id) 
    WHERE u."pId" = :userId AND g.id = :Group AND m.id = :TypeFinancialStatement AND ( cri.agno_mes BETWEEN :dateFrom AND :dateTo ) 
    GROUP BY 1,2,3,4,5,6,7,14,15

You should ask a new question. Simplify the query. Provide sample data and desired results. It is unclear what you really want to do. For instance, is an aggregation query really appropriate. — Gordon Linoff

GMB GMB · Accepted Answer · 2020-04-23T08:57:19

Window function do not require a group by clause. So, most likely, you just need to remove the group by clause from your query.

Alternatively, you want a regular aggregate function (sum() without an over() clause), and all other columns from the select clause in the group by clause - but that's not what your query seems to intend.

Also as far as concerns you don't need that frame defintion in the over clause: assuming that c.id is a unique column, you get the same behavior with just:

SUM(cri.importe_cal * gc.signo) over (order by c.Id) AS Amount

Error 42803. column XXXX must showing clause GROUP BY or be use in an aggregation function

1 Answers