column "pedroad.id" must appear in the GROUP BY clause or be used in an aggregate function

Question

select di.seq, di.node , di.edge , di.cost, a.geom
from pgr_dijkstra(
    'select id, target, source, sum(cost) from pedroad', 
     array(select get_source2('location1'))
     ,array(select get_target2('test4'))
     ,false) as di, 
    pedroad a
    where di.node = a.source;

error: column "pedroad.id" must appear in the GROUP BY clause or be used in an aggregate function

How should I use group by?

Seems like you are using [`pgr_dijkstra()` from pgRouting](http://docs.pgrouting.org/latest/en/src/dijkstra/doc/pgr_dijkstra.html#pgr-dijkstra) which should be mentioned in your question. I tagged accordingly. — Erwin Brandstetter, Dec 15 '16 at 05:11

score 0 · Answer 1 · edited May 23 '17 at 12:24

There is at least one syntax error in the SQL string you are passing as first parameter: sum(cost) is an aggregate function, so all other columns in the SELECT list must appear in the GROUP BY clause or be used in an aggregate function - like the error message says.

This would fix the syntax error:

SELECT di.seq, di.node, di.edge, di.cost, a.geom
FROM   pgr_dijkstra('select id, target, source, sum(cost) from pedroad
                     group by 1,2,3'
                  , array(select get_source2('location1'))
                  , array(select get_target2('test4'))
                  , false) di 
JOIN   pedroad a ON di.node = a.source;

But its unclear how you actually wanted to sum ...

If id happens to be the PK of pedroad, you can simplify to just group by 1.
Explanation:

column "pedroad.id" must appear in the GROUP BY clause or be used in an aggregate function

1 Answers1