1

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 finish
GROUP BY number

I 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?

2

Try this:

SELECT number,
  SUM(CASE WHEN roads.status IN  ('G','V') THEN ST_Length(geom) ELSE NULL END)  as not_started,
  SUM(CASE WHEN roads.status IN  ('T','V') THEN ST_Length(geom) ELSE NULL END)  as started,
  SUM(CASE WHEN roads.status IN  ('D') THEN ST_Length(geom) ELSE NULL END)  as finish
FROM schema.roads
GROUP BY number;

If you are running PostgreSQL 9.4+, you can use the more succinct FILTER construct:

SUM( ST_Length(geom) ) FILTER (WHERE roads.status IN  ('G','V') )
  • Thank you that was just what I was looking for! :) Cheers mate – geogrow Oct 26 '15 at 8:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.