I have a road network where each link is part of a reference number.I am trying to sum up the length of roads per reference number and status.My nested query fails and just sum the total length of roads for each status category and are not split into reference number group.
SELECT number,(sum(ST_Length(geom)) FROM schema.roads WHERE roads.status IN ('G','V')) as not_started(sum(ST_Length(geom)) FROM schema.roads WHERE roads.status IN ('T','V')) as started(sum(ST_Length(geom)) FROM schema.roads WHERE roads.status IN ('D')) as finishGROUP BY numberI believe I have to build a left join query for this, but using functions and group by within a left join query is a bit too complex with my current experience. Could anyone please have give me some suggestions?
أكثر...
SELECT number,(sum(ST_Length(geom)) FROM schema.roads WHERE roads.status IN ('G','V')) as not_started(sum(ST_Length(geom)) FROM schema.roads WHERE roads.status IN ('T','V')) as started(sum(ST_Length(geom)) FROM schema.roads WHERE roads.status IN ('D')) as finishGROUP BY numberI believe I have to build a left join query for this, but using functions and group by within a left join query is a bit too complex with my current experience. Could anyone please have give me some suggestions?
أكثر...