How to select data with group by and subquery calculations?

I have two tables:
list_table:

vt_table:

I need this result:
amount (dir_id = 1 – dir_id = 2), list_id

Amount is sum of all amount fields in table vt_table where direction_id = 1 minus sum of all amount fileds in table vt_table where direction_id = 2

And I need group this calculations by list_id, and if table have no rows with list_id 3, as example, amount must be 0.

I’m trying to do it with this query:

SELECT vt.list_id
     , ((SELECT COALESCE(SUM(vt.amount), 0)
             FROM table_name       vt
             WHERE vt.direction_id = 1)
    -
        (SELECT COALESCE(SUM(vt.amount), 0)
             FROM table_name       vt
             WHERE direction_id = 2)) AS result
    FROM table_name vt
    GROUP BY vt.list_id

But I don’t know how to group it correctly and make it so that if there were no entries for some list_id, then the amount was 0 for this list_id.

I use PostgreSQL 12.

Here the examples

>Solution :

You can try to use OUTER JOIN with condition aggregate function with COALESCE fucntion.

Query 1:

SELECT l.id, 
     SUM(COALESCE(CASE WHEN vt.direction_id = 1 THEN vt.amount END,0)) - 
       SUM(COALESCE(CASE WHEN vt.direction_id = 2 THEN vt.amount END,0)) AS result
FROM table_name vt
RIGHT JOIN list l ON vt.list_id = l.id
GROUP BY l.id
ORDER BY l.id

Results:

| id | result |
|----|--------|
|  1 |     41 |
|  2 |     23 |
|  3 |      0 |