0

I have a query like this 

SELECT trx.store, trx.sales, trx.qty_sold, trx.average, trx.start_date, trx.end_date, stok.stok_qty FROM (select sto.name as store, sum(odi.subtotal_price) as sales, sum(odi.qty) as qty_sold, ((sum(odi.subtotal_price))/(sum(odi.qty))) as average, min(CAST(ord.date_out AS date)) as start_date, max(CAST(ord.date_out AS date)) as end_date from trx_order_detail_item odi left join trx_order as ord on ord.id = odi.order_id left join mst_store as sto on sto.id = ord.store_id where sto.principle_id = 12 and ord.date_out between '2018-07-01' and '2019-02-28' and ord.order_status_id in (select mst_order_status.id from mst_order_status where mst_order_status.id = 3 or mst_order_status.id = 5) and ord.void_status=0 group by sto.name, date_trunc('month', ord.date_out) order by date_trunc('month', ord.date_out)) AS trx, (select sum(siss.jumlah_akhir) as stok_qty from trx_stok_item_summary as siss where siss.input_date IN (select max(sisss.input_date) from trx_stok_item_summary as sisss join mst_store as sto on sto.id = sisss.store_id join mst_item as it on it.id = sisss.item_id join mst_item_classifiers as ic on ic.id = sisss.item_variant_id where sto.principle_id = 12 and input_date between '2018-07-01' AND '2019-02-28' group by date_trunc('month', sisss.input_date), sisss.item_variant_id order by date_trunc('month', sisss.input_date)) group by date_trunc('month', siss.input_date)) AS stok

and the output is

Store |Sales |qty_sold|average |start_date |end_date |stok_qty =================================================================== Store 1|494000|128 |3859.375|"2018-07-02"|"2018-07-31"|-263 Store 1|494000|128 |3859.375|"2018-07-02"|"2018-07-31"|0 Store 1|327000|44 |7431.818|"2018-08-01"|"2018-08-19"|-263 Store 1|327000|44 |7431.818|"2018-08-01"|"2018-08-19"|0

and when I extend the date the output is looping as much as how many month in the data range.

And the output that I want is:

Store |Sales |qty_sold|average |start_date |end_date |stok_qty =================================================================== Store 1|494000|128 |3859.375|"2018-07-02"|"2018-07-31"|-263 Store 1|327000|44 |7431.818|"2018-08-01"|"2018-08-19"|0

any idea how to fix this?

Improve this question
1
  • it looks stok_qty is missed place or you have any logic to pick that Commented Mar 13, 2019 at 9:12

3 Answers 3

Reset to default
1

You're missing any sort of join criteria, try:

SELECT trx.store, trx.sales, trx.qty_sold, trx.average, trx.start_date, trx.end_date, stok.stok_qty FROM (select sto.name as store, sum(odi.subtotal_price) as sales, sum(odi.qty) as qty_sold, ((sum(odi.subtotal_price))/(sum(odi.qty))) as average, min(CAST(ord.date_out AS date)) as start_date, max(CAST(ord.date_out AS date)) as end_date, date_trunc('month', ord.date_out) AS trx_month from trx_order_detail_item odi left join trx_order as ord on ord.id = odi.order_id left join mst_store as sto on sto.id = ord.store_id where sto.principle_id = 12 and ord.date_out between '2018-07-01' and '2019-02-28' and ord.order_status_id in (select mst_order_status.id from mst_order_status where mst_order_status.id = 3 or mst_order_status.id = 5) and ord.void_status=0 group by sto.name, date_trunc('month', ord.date_out) order by date_trunc('month', ord.date_out)) AS trx, (select date_trunc('month', siss.input_date) AS stok_month, sum(siss.jumlah_akhir) as stok_qty from trx_stok_item_summary as siss where siss.input_date IN (select max(sisss.input_date) from trx_stok_item_summary as sisss join mst_store as sto on sto.id = sisss.store_id join mst_item as it on it.id = sisss.item_id join mst_item_classifiers as ic on ic.id = sisss.item_variant_id where sto.principle_id = 12 and input_date between '2018-07-01' AND '2019-02-28' group by date_trunc('month', sisss.input_date), sisss.item_variant_id order by date_trunc('month', sisss.input_date)) group by date_trunc('month', siss.input_date)) AS stok WHERE stok.stok_month = trx.trx_month
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

use distinct

SELECT distinct trx.store, trx.sales, trx.qty_sold, trx.average, trx.start_date, trx.end_date, stok.stok_qty FROM (select sto.name as store, sum(odi.subtotal_price) as sales, sum(odi.qty) as qty_sold, ((sum(odi.subtotal_price))/(sum(odi.qty))) as average, min(CAST(ord.date_out AS date)) as start_date, max(CAST(ord.date_out AS date)) as end_date from trx_order_detail_item odi left join trx_order as ord on ord.id = odi.order_id left join mst_store as sto on sto.id = ord.store_id where sto.principle_id = 12 and ord.date_out between '2018-07-01' and '2019-02-28' and ord.order_status_id in (select mst_order_status.id from mst_order_status where mst_order_status.id = 3 or mst_order_status.id = 5) and ord.void_status=0 group by sto.name, date_trunc('month', ord.date_out) order by date_trunc('month', ord.date_out)) AS trx, (select sum(siss.jumlah_akhir) as stok_qty from trx_stok_item_summary as siss where siss.input_date IN (select max(sisss.input_date) from trx_stok_item_summary as sisss join mst_store as sto on sto.id = sisss.store_id join mst_item as it on it.id = sisss.item_id join mst_item_classifiers as ic on ic.id = sisss.item_variant_id where sto.principle_id = 12 and input_date between '2018-07-01' AND '2019-02-28' group by date_trunc('month', sisss.input_date), sisss.item_variant_id order by date_trunc('month', sisss.input_date)) group by date_trunc('month', siss.input_date)) AS stok
Improve this answer

1 Comment

"stok_qty" is different, distinct will not help
0

Use distinct on:

select distinct on (store, start_date) . . . <rest of your query here> order by store, start_date, stok_qty;
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.