CODEWITHSUNDEEP
sqlpostgresql
Shamji Solanki
Shamji Solanki

Reputation: 25

Multiple select query using postgresql

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

Upvotes: 1

Views: 80

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125464

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');

Upvotes: 1

Related Questions