Question: PostGIS database contains several geometry (polyline) tables (with names "D1\_r", "D2\_r", "D3\_r"). I calculate length for one "postgis layer" use request:

```
SELECT 
sum(ST_Length_Spheroid(the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000 AS km_roads 
FROM 
"D1_r";

```

How to make a request to get a result for each table ("D1\_r", "D2\_r", "D3\_r") and the total result for all tables ("D1\_r"+"D2\_r"+"D3\_r")? 
Thanks!

Answer: 
Unioning the tables together is one way:

```
WITH alltables AS (
  SELECT the_geom FROM D1_r 
  UNION ALL
  SELECT the_geom FROM D2_r 
  UNION ALL
  SELECT the_geom FROM D3_r 
)
SELECT sum(ST_Length_Spheroid(the_geom,'SPHEROID["WGS 84",6378137,298.257223563]'))/1000 AS km_roads
FROM alltables;

```

Incidentally if you want PostGIS 1.5+ you can use the Geography type and get a simpler looking query:

```
WITH alltables AS (
  SELECT the_geom::geography FROM D1_r 
  UNION ALL
  SELECT the_geom::geography FROM D2_r 
  UNION ALL
  SELECT the_geom::geography FROM D3_r 
)
SELECT sum(ST_Length(the_geom))/1000 AS km_roads
FROM alltables;

```
```
SELECT sum(length(d1.the_geom)) as d1sum, sum(length(d2.the_geom)) as d2sum, sum(length(d3.the_geom)) as d3sum, (sum(length(d1.the_geom))+(sum(length(d1.the_geom))) as total 
FROM d1 , d2,d3

```

Is one way of doing it: 
EDIT : indeed , this is wrong way to do it