I have a line dataset that has road names and different surface types along different segments of the road. I want to calculate the total length of each surface type for each road.
I've been able to get the groups and counts as I need using virtual layers / SQL
Select Road, SURF_TYPE, count(SURF_TYPE), geometry from road_extract group by Road,SURF_TYPE
Rather than geometry I want to get the total length of each surface type.
I've found one workaround where if I have a column with $length in the the source table I can include sum(length) in the SQL to get the total length for each surface type but is it possible without having this column?
Also is it possible to get this table formatted with the just the lengths like...
Road, Sealed, Unsealed, Floodway, Total Airport, 15 , 0 , 0 , 15 Balonne, 20 , 5 , 0 , 25 Burke, 40 , 20 , 2 , 62
I have tried the following with no luck
select Road, SURF_TYPE, sum(length) from road_extract Group by Grouping sets ( (Road, SURF_TYPE, sum(length)), (Road), (SURF_TYPE), () );