# How to do a kind of selective grouping in a SQL result?

I have some query as `select x1,x2,x3,x4 from foo where bar` resulting something like:

``````CREATE TEMP TABLE foo
AS SELECT * FROM ( VALUES
( '00','01','03','AA' ),
( '01','03','03','BB' ),
( '02','09','05','CC' ),
( '03','08','05','DD' ),
( '03','08','03','EE' ),
( '03','08','03','FF' ),
( '03','08','03','FF' ),
( '03','08','03','FF' ),
( '03','08','09','GG' )
) AS t( x1, x2, x3, x4);
``````

Note the `x3` column: we have `03` twice then `05` twice then `03` again then `09`. I need to calculate a route using the `pgr_ksp` function (PostGIS). Imagine the table here is the result and the `x3` column is the road names. In this case I start on `03` street, walk on it for 2 segments and take the `05` street just to take the `03` street again after.

My problem is I'm showing this segments to the user and I don't like to. The user know nothing about segments of roads and just want to see `take 03 street, 05 street, 03 again and then 09`.

If I just group by, I will lost the `take 05` step between the two `03` and I'll see the all long `03` segments connected: `take 03 street (all 6 segments together), 05 and then 09` and this is wrong.

My question: how can I group the `x3` column as the two first `03`, then group the two `05` and then group `03` again after `05` and then group all tuples left using this method.

Not so fast! I must have a sum of the `x2` column as I group the `x3`. This will be the geometry column I must compute - concat all segments geometry to have the complete way representation. This is the result I want:

``````-----------
| x2 | x3 |
|----|----|
| 04 | 03 |
|----|----|
| 17 | 05 |
|----|----|
| 32 | 03 |
|----|----|
| 08 | 09 |
-----------
``````

`x2` is the way geometry and `x3` is the name of the street or way. In this case I'll have the complete way segment (sum of small pieces) and the way name.

• I believe there is a /more/ pgrouting way of tracking road changes than this. This question also has nothing to do with PostGIS, as-is. Commented Feb 13, 2017 at 16:54
• Agreee. I can't find a PGR tag. No. PGR work at nodes level connecting source points to target points in road segments. All PGR functions give us a list of nodes and don't care if all of them is the same road. Concern to us to make the fine tunning. Commented Feb 13, 2017 at 17:10

``````create temp table tbl as
select * from
(values (1,01,03),(2,03,03),(3,09,05),(4,08,05),(5,08,03),(6,08,03),(7,08,03),(8,08,03),(9,08,09)) t (id, x2,x3);
``````

First you need to generate a partition by every group of X3 values:

This sentence uses LAG function to mark when X3 changes from the previous row.

``````select id, x2, x3, case when coalesce(lag(x3) over (order by id), x3) = x3 then 0 else 1 end c
from tbl
order by id
``````

Then you can establish a partition using:

``````select id, x2, x3, sum(cnt.c) over (order by id) as part
from cnt
order by id
``````

Then simply get the sum of X2 grouping by every partition.

``````with cnt as
(
select id, x2, x3, case when coalesce(lag(x3) over (order by id), x3) = x3 then 0 else 1 end c
from tbl
order by id
), cnt1 as
(
select id, x2, x3, sum(cnt.c) over (order by id) as part
from cnt
order by id
)
select sum(x2) xx2, min(x3) xx3
from cnt1
group by part
order by part
;
``````

The final result:

``````+-----+-----+
|  X2 |  X3 |
+-----+-----+
| 4   | 3   |
+-----+-----+
| 17  | 5   |
+-----+-----+
| 32  | 3   |
+-----+-----+
| 8   | 9   |
+-----+-----+
``````

Check it here: http://rextester.com/YPU13325

• Ok. It is my fault. `ST_Union(first_geom, other_geom)` must have two parameters to concat the geometry lines and I can't use it in exchange of `SUM()`. But based on my example I think your solution will not repeat the `x3` column as @Andrew Brennan do. Commented Feb 13, 2017 at 14:44
• In your question you said sum(x2) @MagnoC . Let me take a look. Commented Feb 13, 2017 at 14:46
• Yes. `mea culpa`. Commented Feb 13, 2017 at 14:47
• Can you edit your question and your desired result? Commented Feb 13, 2017 at 14:48
• Oh I see, two rows for each X2 Commented Feb 13, 2017 at 14:49

You will need some column value that differentiates the x3 values. For example you could group by both x1 and x3, then you will only lose rows where both x1 and x3 are the same.

``````select x1, sum(x2), x3 from foo where bar group by x1, x3.
``````

This would look like:

``````_____________________
|    |         |    |
| x1 | sum(x2) | x3 |
|____|_________|____|
|    |         |    |
| 00 | 01      | 03 |
| 01 | 03      | 03 |
| 02 | 09      | 05 |
| 03 | 08      | 05 |
| 03 | 24      | 03 |
| 03 | 08      | 09 |
|____|_________|____|
``````

For any other output you would need to use programmatic logic to loop through the SQL output, sum x2, and only present new info when x3 changes. You could do this in your application, or you might be able to write a function for it in PostGREs, but I'm pretty sure it is not supported by ANSI SQL.

• Well... `ST_Union` wants two parameters. `ST_Union(first_geom, other_geom)` to concat the lines... seems my own anser solved. I'll just check the geometry conact and post here the result. I'm afraid the overhead will be too much... Commented Feb 13, 2017 at 13:07
• I'll accept your answer because you actually answered what I asked based on the information I provided as an example. Thanks. Commented Feb 13, 2017 at 14:04
• Still getting two `03` in `x3` column ... cannot have equal values in sequence... ;-) Commented Feb 13, 2017 at 14:08
• Right, the x1 column is note entirely appropriate for your usage. Anyway, others have given you PG aggregate functions to do what you want below. Hope you found something that works. Commented Feb 13, 2017 at 14:19
• Yep. Worked, but I'm stuck in the last record. Since `RETURN NEXT;` is inside an `if` I have a record not sent and can't put an `RETURN NEXT;` outside the loop. I'm trying to discover how to solve this to put the complete function here. I'll appreciate if you could try the AG with the data in example to solve the last record problem... Only if you want because I already accepted your answer.. Commented Feb 13, 2017 at 14:30

This function was the one that actualy solved my problem. I'll not accept it because I was not too clear in my question and based in my example the accepted answer is the correct one. The major problem is the `ST_Union` function: I need to store all geometries until the street name is the same. When the street name change I need to send it and a union of all segments geometry. To `ST_Union` I pass the geometry of the current record and the geometry of previously cached lines, and then replace the cache with the new union.

``````CREATE or replace FUNCTION route_agg(
IN source integer,
IN target integer,
IN k integer,
IN directed boolean)
RETURNS TABLE(geom geometry, way_name text, km double precision, seq integer) AS \$\$
DECLARE
var_r record;
tmp_name text;
tmp_geom geometry;
is_first boolean;
counter integer;
BEGIN
tmp_name := '';
is_first := true;
counter := 0;
FOR var_r IN ( select * from calc_rotas_v3(\$1, \$2, \$3, \$4) as r INNER JOIN osm_2po_4pgr as g ON r.edge = g.id order by seq) LOOP

if ( not is_first ) then
if ( tmp_name <> var_r.osm_name  ) then
geom := tmp_geom;
way_name = tmp_name;
km = ST_Length(tmp_geom::geography)/1000;

tmp_name := var_r.osm_name;
tmp_geom := var_r.geom_way;

seq = counter;
counter = counter + 1;
RETURN NEXT;
else
tmp_geom := ST_Union(tmp_geom, var_r.geom_way);
end if;
else
tmp_name := var_r.osm_name;
tmp_geom := var_r.geom_way;
is_first := false;
end if;

END LOOP;

geom := tmp_geom;
way_name = tmp_name;
km = ST_Length(tmp_geom::geography)/1000;
seq = counter;
RETURN NEXT;

END; \$\$
LANGUAGE 'plpgsql' VOLATILE;
``````