sql - I want to join two aggregrated result sets -


my query sum of total qty , sum of total returned qty. got answers individually need combine.

select c.city_name       ,pc.prod_cat_name       ,to_char(th.txn_date, 'w')       ,to_char(th.txn_date, 'yyyy')       ,sum(td.qty)   city      c       ,prod_cat  pc       ,product   p       ,txn_hdr   th       ,txn_det   td       ,store_det s       ,state     st  c.state_id = st.state_id    , s.city_id = c.city_id    , s.store_id = th.store_idand th.txn_hdr_id = td.txn_hdr_id    , td.prod_id = p.prod_id    , p.prod_cat_id = pc.prod_cat_id  group c.city_name          ,pc.prod_cat_name          ,to_char(th.txn_date, 'w')          ,to_char(th.txn_date, 'yyyy') 

output

bangalore   electronics 1   2015    1 bangalore   electronics 3   2015    1 bangalore   clothing    2   2015    1 bangalore   clothing    1   2015    2 chennai     stationary  1   2015    10 chennai     cars_bike   4   2015    5 bangalore   clothing    3   2015    4 

second query

select c.city_name       ,pc.prod_cat_name       ,to_char(th.txn_date, 'ww')       ,to_char(th.txn_date, 'yyyy')       ,sum(returned_qty)   city       c       ,prod_cat   pc       ,store_det  st       ,txn_hdr    th       ,txn_det    td       ,product    p       ,return_hdr rh       ,return_det rd  th.txn_hdr_id = td.txn_hdr_id    , th.store_id = st.store_id    , st.city_id = c.city_id    , td.prod_id = p.prod_id    , p.prod_cat_id = pc.prod_cat_id    , rh.txn_hdr_id = th.txn_hdr_id    , rh.return_hdr_id = rd.ret_hdr_id  group c.city_name          ,pc.prod_cat_name          ,to_char(th.txn_date, 'ww')          ,to_char(th.txn_date, 'yyyy') 

output

bangalore   clothing    37  2015    1  bangalore   electronics 28  2015    1 

i want 1 table total qty , total returned qty..

do mean this?

select c.city_name, pc.prod_cat_name,        to_char (th.txn_date, 'w') wom,        to_char (th.txn_date, 'yyyy') yr,        sum (td.qty) qty,        null woy,         null rqty ... union select c.city_name, pc.prod_cat_name,        null wom,        to_char (th.txn_date, 'yyyy') yr,        null qty,        to_char (th.txn_date, 'ww') woy,        sum (returned_qty) rqty ... 

Comments

Popular posts from this blog

resizing Telegram inline keyboard -

command line - How can a Python program background itself? -

php - "cURL error 28: Resolving timed out" on Wordpress on Azure App Service on Linux -