Calculating lengths and percentages of potholes with SQL query in QGIS

Question

I have a shapefile in QGIS that holds information about the amount and severity of potholes on a road network across several cities:

• The road network is split into segments of different lengths (meters)
• Each segment is split into 10m parts
• Each part has potholes measured in percentage of 10 meters (zero, mild, important and severe potholes)
• Each city has several segments of the road network (city zip code)

I would like to have for each city the percentages of pothole types compared to the total length of the city segments. Is possible to do this in one SQL query?

Answer 1

• Total length of city segments :

SELECT
  pt.zip,
  SUM(pt.segment_length) AS total_segment_length
FROM (
SELECT DISTINCT
  "CITY ZIP CODE" AS zip,
  "SEGMENT ID" AS segment_id,
  "SEGMENT LENGTH" AS segment_length
FROM potholes_table
) pt
GROUP BY pt.zip
;

• Potholes types :

SELECT
  "CITY ZIP CODE" AS zip,
  SUM("MILD POTHOLES" * 10) AS mild,
  SUM("IMPORTANT POTHOLES" * 10) AS important,
  SUM("SEVERE POTHOLES" * 10) AS severe
FROM potholes_table
GROUP BY "CITY ZIP CODE"

• Combine the two :

WITH total_length AS (
  SELECT
    pt.zip,
    SUM(pt.segment_length) AS total_segment_length
  FROM (
  SELECT DISTINCT
    "CITY ZIP CODE" AS zip,
    "SEGMENT ID" AS segment_id,
    "SEGMENT LENGTH" AS segment_length
  FROM potholes_table
  ) pt
  GROUP BY pt.zip
),
types_length AS (
  SELECT
    "CITY ZIP CODE" AS zip,
    SUM("MILD POTHOLES" * 10) AS mild,
    SUM("IMPORTANT POTHOLES" * 10) AS important,
    SUM("SEVERE POTHOLES" * 10) AS severe
  FROM potholes_table
  GROUP BY "CITY ZIP CODE"
)
SELECT
  tot.zip,
  tot.total_segment_length,
  typ.mild / tot.total_segment_length * 100 AS mild_perc,
  typ.important / tot.total_segment_length * 100 AS important_perc,
  typ.severe / tot.total_segment_length * 100 AS severe_perc
FROM
  total_length tot
  INNER JOIN types_length typ ON tot.zip = typ.zip
;

Answer 2

If by "each city" you refer to each distinct value in "CITY ZIP CODE", the query would simply be:

SELECT
  "CITY ZIP CODE",
  SUM("MILD POTHOLES") / (COUNT(*) * 10) AS mp_pct,
  SUM("IMPORTANT POTHOLES") / (COUNT(*) * 10) AS ip_pct,
  SUM("SEVERE POTHOLES") / (COUNT(*) * 10) AS sp_pct
FROM
  <potholes>
GROUP BY
  1
;

---

Note:

This assumes that each part is exactly 10m in length - we simply count the parts per ZIP code and multiply by 10. If that is not the case (I mean, how could it, really, IRL - but that's what your question says), go with @J.Monticolo's answer if the values in "SEGMENT LENGTH" are aware of any length remainder, or work with ST_Length of the actual geometries instead.