Multiple select query using postgresql

Question

I have 2 tables

table_a

id  b_ref_id    qty
52  9            13
53  10           20
54  11           25

table_b

id  method       date                state
9   m1          28/07/16             confirmed
10  m1          29/07/16             done
11  m1          30/07/16             waiting

My desire output

m1         today    tomorrow    day_after_tomorrow
waiting     13       0                 0
confirmed   0        20                0
done        0        0                 25

I try with following query but qty is repeat for all

select stock_p.method, stock_p.state, 
(select sm.qty
 from 
    table_a sm
    join table_b spo on (sm.b_ref_id=spo.id)
where
    to_char(spo.date,'YYYY-MM-DD')::date = current_date and ) today_qty,
(select sm.qty
from table_a sm
    join table_b spo on (sm.b_ref_id=spo.id)
where 
    to_char(spo.date,'YYYY-MM-DD')::date = (current_date + 1) ) tomorrow_qty,
(select sm.qty
from table_a sm
    join table_b spo on (sm.b_ref_id=spo.id)
    where
    to_char(spo.date,'YYYY-MM-DD')::date = (current_date + 2)) next_three_qty

from table_a stock_m join table_b stock_p on stock_m.b_ref_id = stock_p.id group by stock_p.method,stock_p.stateenter code here

In my opinion you are trying to do two different things here. Getting the data that you want is easy, It's a straight join on the two tables. What is harder is the pivot you are doing on the data. Which is often best done in a different tool. BTW the query doesn't seem to match the tables. — Michael Robellard
– Michael Robellard, Commented Jul 28, 2016 at 14:31

Clodoaldo Neto · Accepted Answer · 2016-07-28 20:25:01Z

1

select
    t1.method, t1.status,
    sum ((t1.min_date = current_date or null)::int * sm.product_qty) as today,
    sum ((t1.min_date = current_date + 1 or null)::int * sm.product_qty) as tomorrow,
    sum ((t1.min_date = current_date + 2 or null)::int * sm.product_qty) as day_after_tomorrow
from
    stock_move sm
    inner join
    table_1icking t1 on sm.picking_id = t1.id
group by t1.method, t1.status
;
 method |  status   | today | tomorrow | day_after_tomorrow 
--------+-----------+-------+----------+--------------------
 m1     | waiting   |       |          |                 25
 m1     | done      |       |       20 |                   
 m1     | confirmed |    13 |          |

With 9.4+ use filter as commented by @a_horse. The data:

create table stock_move (id int, picking_id int, product_qty int);
insert into stock_move (id, picking_id, product_qty) values
(52,9,13), (53,10,20), (54,11,25);
set datestyle = 'dmy';
create table table_1icking (id int, method text, min_date date, status text);
insert into table_1icking (id, method, min_date, status) values
(9,'m1','28/07/16','confirmed'),
(10,'m1','29/07/16','done'),
(11,'m1','30/07/16','waiting');

edited Jul 28, 2016 at 20:25

answered Jul 28, 2016 at 14:34

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

3 Comments

user330315 Over a year ago

Alternatively: sum(sm.qty) filter (where t1.date = current_date) as today

Shamji Solanki Over a year ago

Hello, thanks for replaying. I did try but its shows qty is 0 in all colums.

Clodoaldo Neto Over a year ago

@user5668640 Works for me. Check the update question with data.

Collectives™ on Stack Overflow

Multiple select query using postgresql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related