Postgres multi row to string and calculate

Question

I have these table :

tblproduk :
|  skuid | namabarang      |
|--------|-----------------|
| 123456 | INDOMIE GORENG  |
| 234567 | COKLAT BENGBENG |
| 345678 | BISKUIT         |
        
tblproduk_satuan:

| id |  skuid | kodebarang | satuan | konversi |  price |
|----|--------|------------|--------|----------|--------|
|  1 | 123456 |       ABC1 |    PCS |        1 |   6000 |
|  2 | 123456 |       ABC2 |    DUS |       20 | 100000 |
|  3 | 234567 |        BCD |    PCS |        1 |   3000 |
|  4 | 345678 |       CDE1 |    BKS |        1 |   4500 |
|  5 | 345678 |       CDE2 |    LSN |       12 |  50000 |
|  6 | 345678 |       CDE3 |    DUS |       48 | 190000 |

tblproduk_stock:

| id |  skuid | awal | masuk | keluar |
|----|--------|------|-------|--------|
|  1 | 123456 |   10 |    50 |     30 |
|  2 | 234567 |    0 |   100 |     20 |
|  3 | 345678 |   20 |   400 |     21 |

Here is the sqlfiddle of my table. What is the the most efficient way to convert multi row to string from tblproduct_satuan, make calculation and display it like this :

|  skuid |  namabarang     | stock | satuan |Remarks          |   Amount
|--------|-----------------|-------|--------|-------------------------------
| 123456 | INDOMIE GORENG  |    30 | PCS    | 1 DUS 10 PCS      |   160.000
| 234567 | COKLAT BENGBENG |    80 | PCS    | 80 PCS            |   240.000
| 345678 | BISKUIT         |   399 | BKS    | 8 DUS 1 LSN 3 BKS | 1.583.500

Hope to get help from the expert.

Thank you

(1) Please explain the logic, especially for columns remarks and amount; it is not obvious at all. (2) Your question would be much easier to follow if you were to translate the object names to English. — GMB
– GMB, Commented Nov 16, 2020 at 11:00
Totally unrelated to your problem, but you might want to read this: Is adding the ‘tbl’ prefix to table names really a problem? — user330315
– user330315, Commented Nov 16, 2020 at 11:04
and the table names within the question do not conform to the names within the demo. — Barbaros Özhan
– Barbaros Özhan, Commented Nov 16, 2020 at 11:40
Sorry for using non standard English object name. "remarks" is the equivalent of stock, converted from the biggest to least unit. One skuid may have 1 or more units. Amount is the calculation of price * remarks. In the table, 399 pcs is equal to 8 box 1 Dozen 3 Pc — Hengky Liwandouw
– Hengky Liwandouw, Commented Nov 16, 2020 at 13:18
Thank you all for trying to help me. This is the updated sql fiddle in standard english name. sqlfiddle.com/#!17/0cf27/1 — Hengky Liwandouw
– Hengky Liwandouw, Commented Nov 16, 2020 at 13:32

Akhilesh Mishra · Accepted Answer · 2020-11-17 20:31:41Z

2

If I understood correctly, Here is the query for your requirement:

WITH CTE AS (
select 
t1.skuid,
t1.namabarang,
t3.masuk+t3.awal-t3.keluar "stock",
t2.satuan,
t2.konversi,
floor(mod((t3.masuk+t3.awal-t3.keluar),coalesce(lag(t2.konversi) over (partition by t1.skuid order by t2.konversi desc ),(t3.masuk+t3.awal-t3.keluar)+1))/t2.konversi) "count_",
t2.price,
row_number() over (partition by t1.skuid order by t2.konversi) "rn"
from 
tblproduct t1 
inner join tblproduct_satuan t2 on t1.skuid=t2.skuid
inner join tblproduct_onhand t3 on t3.skuid=t1.skuid
)
select 
skuid,
namabarang,
stock,
min(satuan) filter (where rn=1) "satuan",
string_agg(concat(count_,' ',satuan), ' ' order by konversi desc) "Remarks",
sum(price*count_) "Amount"
from cte
group by 1,2,3

In With block I have calculated all the required values and then aggregated for final output.

DEMO

answered Nov 17, 2020 at 20:31

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

1 Comment

Hengky Liwandouw Over a year ago

Thank you so much Akhilesh ! This is really what i want to !. This help me a lot.

Collectives™ on Stack Overflow

Postgres multi row to string and calculate

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related