CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
Hengky Liwandouw
Hengky Liwandouw

Reputation: 37

Postgres multi row to string and calculate

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

Upvotes: 1

Views: 118

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

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

Upvotes: 2

Related Questions