CODEWITHSUNDEEP
sqloracle-database
jason Palmer
jason Palmer

Reputation: 43

combining 2 queries into 1 so output is on single row

I'm trying to combine the output of both the below queries so they output on one row any help you can provide will be very much appreciated.

Query 1:

select FLT_NUM, SCH_DEP_DATE, sum (GROSS_WGT) as UWS_GROSS_WGT,  sum (NETT_WGT) + SUM(BULK_WGT) as UWS_NETT_WGT
from
(select distinct  
        d.aln_code||d.flt_num as FLT_NUM, 
        trunc (d.sch_dep_date) flight_date,
        d.brd_pnt, 
        d.off_pnt,
        trunc(d.sch_dep_date) as Departure_date,
        to_date(to_char(d.sch_dep_date, 'DD-MON-YYYY')||''||d.sch_dep_time_hrs||':'||d.sch_dep_time_min, 'DD-MON-YYYY HH24:MI') as SCH_DEP_DATE,
        a.gross_wt as GROSS_WGT, 
        a.tare_wt as TARE_WGT, 
        a.nett_wt as NETT_WGT, 
        a.shpmnt_wt as BULK_WGT


from  cfr_leg d, cfr_uws_hdr e, CFR_LEG_SEG f,CFR_UWS_LOAD_DTLS a
where d.fll_id = e.fll_id (+)
  and d.fll_id = f.fll_id
  and d.fll_id = a.fll_id
  and d.flt_num = '0001'
  and trunc (d.sch_dep_date) between  '29-OCT-2016' and '29-OCT-2016'
  and d.brd_pnt = 'LHR'
  and f.opn_mode = 'J')

group by FLT_NUM, SCH_DEP_DATE

Query 2:

select 

FLT_NUM,SCH_DEP_DATE, sum (BKD_WGT), sum (BKD_VOL)

from
(Select  distinct
         a.aln_code||a.flt_num as FLT_NUM, 
         trunc(a.dep_date)as SCH_DEP_DATE, 
         b.doc_prfx, 
         b.doc_num, 
         a.bkd_wt as BKD_WGT, 
         a.bkd_vol as BKD_VOL     
from sbh_res_itnry a, sbh_car b, sbh_res c
where c.car_id = b.car_id (+)
  and c.car_id = a.car_id 
  and a.aln_code = 'VS'
  and a.flt_num = '0001'
  and trunc (a.dep_date) = '29-OCT-2016'
  AND a.cncl_ind = 'N'
  and b.cncl_ind = 'N'
  and c.enq_ind = 'N')

group by FLT_NUM,SCH_DEP_DATE

I have tried using union all but it outputs the data over 2 rows

VS0001  29-Oct-2016             21365.09    92.404281
VS0001  29-Oct-2016             22595       21907

I would like to see output as follows:

FLT_NUM SCH_DEP_DATE   UWS_GROSS_WGT  UWS_NETT_WGT   BKD WGT  BKD_VOL
VS0001  29-Oct-2016    21365.09        92.404281       22595   21907

Upvotes: 4

Views: 77

Answers (2)

Kacper
Kacper

Reputation: 4818

Isn't it enough?

with q1 as (/*YOUR QUERY 1*/), q2 as (/*YOUR QUERY 2*/)
select q1.FLT_NUM, q1.SCH_DEP_DATE, q1.UWS_GROSS_WGT, q1.UWS_NETT_WGT, q2."BKD WGT", q2.BKD_VOL 
from q1 inner join q2 on (q1.FLT_NUM = q2.FLT_NUM and q1.SCH_DEP_DATE = q2.SCH_DEP_DATE)

Full query:

with q1 as (select FLT_NUM, SCH_DEP_DATE, sum (GROSS_WGT) as UWS_GROSS_WGT,  sum (NETT_WGT) + SUM(BULK_WGT) as UWS_NETT_WGT
from
(select distinct  
        d.aln_code||d.flt_num as FLT_NUM, 
        trunc (d.sch_dep_date) flight_date,
        d.brd_pnt, 
        d.off_pnt,
        trunc(d.sch_dep_date) as Departure_date,
        to_date(to_char(d.sch_dep_date, 'DD-MON-YYYY')||''||d.sch_dep_time_hrs||':'||d.sch_dep_time_min, 'DD-MON-YYYY HH24:MI') as SCH_DEP_DATE,
        a.gross_wt as GROSS_WGT, 
        a.tare_wt as TARE_WGT, 
        a.nett_wt as NETT_WGT, 
        a.shpmnt_wt as BULK_WGT


from  cfr_leg d, cfr_uws_hdr e, CFR_LEG_SEG f,CFR_UWS_LOAD_DTLS a
where d.fll_id = e.fll_id (+)
  and d.fll_id = f.fll_id
  and d.fll_id = a.fll_id
  and d.flt_num = '0001'
  and trunc (d.sch_dep_date) between  '29-OCT-2016' and '29-OCT-2016'
  and d.brd_pnt = 'LHR'
  and f.opn_mode = 'J')

group by FLT_NUM, SCH_DEP_DATE), 
q2 as (select 

FLT_NUM,SCH_DEP_DATE, sum (BKD_WGT) BKD_WGT, sum (BKD_VOL) BKD_VOL

from
(Select  distinct
         a.aln_code||a.flt_num as FLT_NUM, 
         trunc(a.dep_date)as SCH_DEP_DATE, 
         b.doc_prfx, 
         b.doc_num, 
         a.bkd_wt as BKD_WGT, 
         a.bkd_vol as BKD_VOL     
from sbh_res_itnry a, sbh_car b, sbh_res c
where c.car_id = b.car_id (+)
  and c.car_id = a.car_id 
  and a.aln_code = 'VS'
  and a.flt_num = '0001'
  and trunc (a.dep_date) = '29-OCT-2016'
  AND a.cncl_ind = 'N'
  and b.cncl_ind = 'N'
  and c.enq_ind = 'N')

group by FLT_NUM,SCH_DEP_DATE)
select q1.FLT_NUM, q1.SCH_DEP_DATE, q1.UWS_GROSS_WGT, q1.UWS_NETT_WGT, q2.BKD_WGT, q2.BKD_VOL 
from q1 inner join q2 on (q1.FLT_NUM = q2.FLT_NUM and q1.SCH_DEP_DATE = q2.SCH_DEP_DATE);

Upvotes: 3

Thomas
Thomas

Reputation: 366

or just modify your union like the following pseudo code:

    SELECT FLT_NUM, SCH_DEP_DATE, sum(UWS_GROSS_WGT),  sum(UWS_NETT_WGT),   sum(BKD WGT),  sum(BKD_VOL)
    FROM( 
        select    FLT_NUM, SCH_DEP_DATE,   UWS_GROSS_WGT,  UWS_NETT_WGT,  0 as BKD WGT , 0 as  BKD_VOL from ...
            union
        select    FLT_NUM, SCH_DEP_DATE,   0 as UWS_GROSS_WGT,  0 as UWS_NETT_WGT,   BKD WGT ,    BKD_VOL from ...
        )
    GROUP BY FLT_NUM, SCH_DEP_DATE

Upvotes: 0

Related Questions