Find smallest value connected with arrays in 2 tables SQL Postgres

Question

I have table A with columns id (integer), group (_int8 -> array) and table B with id (int), group (_int8 -> array) and price (money). I have to find the smallest amount of money for each value in array in table A. For example:

Table A :

id | group
---|-------
 0 | {3,4,5}
 1 | {3,6}

Table B :

id | group |price
---|-------|------
5| {1}|100
6| {3,4}| 200
7| {3,5}| 300
8| {4,6}| 100

So for first record (id 0) i check money with first value (3) in all records containing that value. So it's with id 6 and 7. Next I have to return smallest of them so it's 200 (6). Next i do the same for second value (4) and it's 100, next is 5 and money is 300.

Next another row (id 1) so for 3 it's 200 and for 6 it's 100.

Now i have to sum all of that -> 200 + 100 + 300 +200 + 100 = 900

How can i do it in postgres?

Min price for item 5 is 300, not 100 and so the sum is 900.

Stefanov.sm
– Stefanov.sm

2022-09-08 11:44:31 +00:00
Commented Sep 8, 2022 at 11:44 — Stefanov.sm
– Stefanov.sm, Commented Sep 8, 2022 at 11:44

Stefanov.sm · Accepted Answer · 2022-09-09 06:46:17Z

1

Here it is. Plese note that table a is first normalized (or flattened) using a lateral join.

select sum(minprice) from
(
 select v,
   (select min(price) from b where v = any("group")) as minprice
 from a cross join lateral unnest("group") as af(v)
) as t;

edited Sep 9, 2022 at 6:46

answered Sep 8, 2022 at 11:50

Stefanov.sm

13.3k2 gold badges25 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Find smallest value connected with arrays in 2 tables SQL Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related