How to group by two slices?

Question

PostgreSQL

Suppose we have a table of shapes:

CREATE TABLE fig(
  id BIGINT PRIMARY KEY,
  title VARCHAR
)

Vertex table:

CREATE TABLE vertex(
  id BIGINT PRIMARY KEY,
  fig_id BIGINT,
  vertex_cost INTEGER -- стоимость - некое свойство вершины
)

Side table:

CREATE TABLE edge (
  id BIGINT PRIMARY KEY,
  fig_id BIGINT,
  edge_cost INTEGER -- стоимость - некое свойство грани
)

Question 1:
Is it possible to get a list of shapes without nested queries and for each shape, the sum of the costs of its vertices and the sum of the costs of its faces?
Using nested queries, the problem can be solved like this:

-- пример правильного, но не удовлетворяющего основному условию задачи, решения:
SELECT 
  fig.*,
  (SELECT SUM(vertex_cost) FROM vertex WHERE vertex.fig_id = fig.id) vertex_sum,
  (SELECT SUM(edge_cost) FROM edge WHERE edge.fig_id = fig.id) edge_sum
FROM fig

Question 2 (with an asterisk): Without nested
queries, is it possible to get a list of shapes and for each shape, the sum of the costs of its vertices and the sum of the costs of the faces, as well as the minimum and maximum costs? Using nested queries, the problem can be solved like this:

-- пример правильного, но не удовлетворяющего основному условию задачи, решения:
SELECT 
  fig.*,
  (SELECT SUM(vertex_cost) FROM vertex WHERE vertex.fig_id = fig.id) vertex_sum,
  (SELECT SUM(edge_cost) FROM edge WHERE edge.fig_id = fig.id) edge_sum,
  (SELECT MIN(vertex_cost) FROM vertex WHERE vertex.fig_id = fig.id) vertex_min,
  (SELECT MIN(edge_cost) FROM edge WHERE edge.fig_id = fig.id) edge_min,
  (SELECT MAX(vertex_cost) FROM vertex WHERE vertex.fig_id = fig.id) vertex_max,
  (SELECT MAX(edge_cost) FROM edge WHERE edge.fig_id = fig.id) edge_max
FROM fig

Answer 1

SELECT f.* ,SUM(ISNULL(edge_cost,0)) as edge, SUM(ISNULL(vertex_cost,0)) as vortex 
FROM fig as f
LEFT JOIN vertex as v ON f.id = v.fig_id
LEFT JOIN edge as e ON f.id = e.fig_id
GROUP BY f.id

It could be something like this. Similarly, to be smart with the second task.

Answer 2

with vertex_cost as (
select fid_id, sum(vertex_cost) as vertex_cost /*min, max аналогично*/ from vertex group by fid_id
),
edge_cost as (
select fid_id, sum(edge_cost) as edge_cost /*min, max*/ from edge group by fid_id
)
select id, name, vertex_cost, edge_cost
    from fig
    left join vertex_cost vc on fig.id=vc.fig_id
    left join edge_cost ec on fig.id=ec.fig_id

If the figures always have bones, then replace left join with join. Now the figure will always be returned, but with NULL.
If you need to pre-filter the shapes themselves:

with figures as (
select id, name from figures where name like 'foo%'
),
vertex_cost as (
select fid_id, sum(vertex_cost) as vertex_cost /*min, max*/ from vertex where fig_id in (select id from figures) group by fid_id
),
edge_cost as (
select fid_id, sum(edge_cost) as edge_cost /*min, max*/ from edge where fig_id in (select id from figures) group by fid_id
)
select id, name, vertex_cost, edge_cost
    from figures
    left join vertex_cost vc on figures.id=vc.fig_id
    left join edge_cost ec on figures.id=ec.fig_id

Answer 3

SELECT
  fig.id,
  SUM(vertex_cost) / COUNT(DISTINCT edge.id) vertex_sum,
  SUM(edge_cost) / COUNT(DISTINCT vertex.id) ege_sum,
  MIN(vertex_cost) vertex_min,
  MIN(edge_cost) edge_min,	
  MAX(vertex_cost) vertex_max,
  MAX(edge_cost) edge_max		
FROM 
  fig
  JOIN vertex
    ON fig.id = vertex.fig_id
  JOIN edge 
    ON fig.id = edge.fig_id
GROUP BY
  fig.id