Temporary tables and joining queries.

-- Select trip id and the sum of the link costs.
SELECT trip_id, SUM(link_cost) AS travel_time
FROM 
-- Using the selected trips query as one table in the main query.
(
-- Generate some random test data for trips.
WITH selected_trips AS
(
	SELECT unnest('{A,A,B,B,B,C,C,C,C}':: character varying[]) AS trip_id,
	(random() * 10+1) :: integer AS segment_id
)
SELECT *
FROM selected_trips
ORDER BY trip_id
) AS trips -- Note: The generated result needs to be named.

-- Join the link travel time query as a secondary table in the main query.
-- Note: In this case I assume that all segments in the trip exists in the link cost table.
--       If a segment is missing the total travel time will be lower than expected.
INNER JOIN
(
-- Generate some random test data for link costs.
WITH avg_link_time AS
(
	SELECT generate_series(1,10) AS segment_id,
 		   Interval '1 minute' * random() * 10 :: integer AS link_cost
)
SELECT segment_id, avg(link_cost) AS link_cost
FROM avg_link_time
GROUP BY segment_id
ORDER BY segment_id) AS link_time -- Note: The generated result needs to be named.

-- Defining the relation between the two result tables, trips and link_time.
ON(trips.segment_id = link_time.segment_id)
-- Grouping and ordering for the main query.
GROUP BY trip_id
ORDER BY trip_id