CODEWITHSUNDEEP
sqloracle-databasegroup-byoracle11g
Sameer Deshpande
Sameer Deshpande

Reputation: 1

Result set in Oracle with Union

I have made union with two query in Oracle SQL

SELECT account_number, SUM(amount_settled)    
  FROM table W
 WHERE account_number= &acc
   AND Component = 'PROFIT_1'
 GROUP BY account_number
 UNION 
SELECT account_number, SUM(amount)  
  FROM table S
 WHERE account_number=  &acc
   AND amount_tag= 'PROFIT_2' 
 GROUP BY account_number 
 ORDER BY account_number

I am getting value in result set as one below the other

SUM(AMOUNT_SETTLED)
1   36260.16
2   36342.16

I want to display in single row on beside the other

SUM(AMOUNT_SETTLED)       SUM(E.AMOUNT)
36260.16                  36342.16

how to achieve this with two Different Table in Oracle

Upvotes: 0

Views: 34

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

Seems this is your current case ( without the lines those contain substitution variables ) :

SELECT account_number, SUM(amount_settled)    
  FROM table1 t1
 WHERE component = 'PROFIT_1'
 GROUP BY account_number
 UNION 
SELECT account_number, SUM(amount)  
  FROM table2 t2
 WHERE amount_tag= 'PROFIT_2' 
 GROUP BY account_number

and you might need to use CROSS JOIN among the subqueries derived individually from each tables through use of conditional aggregation :

SELECT sum_amount_settled, sum_amount
  FROM ( SELECT SUM(CASE WHEN component = 'PROFIT_1' THEN amount_settled END) AS sum_amount_settled   
           FROM table1) 
 CROSS JOIN ( SELECT SUM(CASE WHEN amount_tag = 'PROFIT_2' THEN amount END) AS sum_amount  
                FROM table2)

Demo

Upvotes: 0

Related Questions