I have this query the table flights also contains price column. I'd like to sum it all up and display. How can I solve this? Can I do this by taking the values from SELECT * from get_cities; somehow or it should be done in the query?

Table img

I am trying to solve this

Write a query finding all the names of the cities City name can be reached by plane with 3 stops. Display all the cities where the stop took place and the total cost of the trip. Also sum up the journey cost.

WITH RECURSIVE get_cities AS (
    SELECT 0 as count, city, cid from cities where CITY = 'Agat'
    UNION ALL
    SELECT c.count   1, b.city, b.cid from get_cities c
    JOIN flights t on t.departure = c.cid
    JOIN cities b on t.arrival = b.cid
    WHERE COUNT < 3
)

SELECT cid, sum(price) from get_cities
    JOIN flights f on f.fid = cid
    GROUP BY cid
;

CodePudding user response：

You can sum the prices directly in the recursive cte :

WITH RECURSIVE get_cities AS (
    SELECT 0 as count, array[city] as city_path, array[cid] as cid_path, 0 as total_price 
      FROM cities 
     WHERE CITY = 'Agat'
    UNION ALL
    SELECT c.count   1, c.city_path || b.city, c.cid_path || b.cid, c.total_price   t.price
      FROM get_cities c
      JOIN flights t on t.departure = c.cid
      JOIN cities b on t.arrival = b.cid
     WHERE COUNT < 3
)
SELECT *
  FROM get_cities
 WHERE count = 2  -- select only the journey with 3 stops ;