How to select data with group by and subquery calculations?

user_17090633 0
Viewed 39

I have two tables: list_table:

id name
1 a
2 b
3 c

vt_table:

id list_id amount direction_id
1 1 20 1
2 1 12 2
3 1 15 1
4 2 23 1
5 1 20 1
6 1 20 2
7 1 18 1

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

amount list_id
41 1
23 2
0 3

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

postgresql sql
3 Answers

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 |
user_51760710

Try something like this, as a start:

SELECT vt.list_id
     , COALESCE(SUM(CASE WHEN direction_id = 1 THEN amount END), 0)
     - COALESCE(SUM(CASE WHEN direction_id = 2 THEN amount END), 0) AS result
  FROM table_name vt
 GROUP BY vt.list_id
;

Result using your fiddle:

list_id result
1 41
2 23

This just misses the cases where there are no vt rows for some list.

Use an outer join to address those cases.

user_24456290 
    SELECT SUM(CASE WHEN vt.direction_id = 1 THEN vt.amount ELSE 0 END) - SUM(CASE WHEN vt.direction_id = 2 THEN vt.amount ELSE 0 END) as amount,
    lt.id as list_id
    FROM  list_table lt
    LEFT OUTER JOIN vt_table vt
    ON   lt.id = vt.list_id
    GROUP BY lt.id
ORDER BY lt.id
user_81872530
Related