Join 2 sql outputs with no common column and both having sum()

user_19441521 0
Viewed 24

So I'm trying to join 2 table outputs which has no common column but same column name.

SQL> SELECT sum(s.bytes/1024/1024/1024) AS "Total Space"
FROM dba_data_files s
UNION all
SELECT sum(d.bytes/1024/1024/1024) AS "Used space"
FROM dba_segments d  2    3    4    5

  6  ;

Total Space
----------
90.9035645
   74.4375

When I'm using cross join I'm getting incorrect output:

SELECT sum(s.bytes/1024/1024/1024) AS "Total Size", sum(d.bytes/1024/1024/1024) AS "Used space"
FROM dba_data_files s
CROSS JOIN dba_segments d;

  2    3

Total Size Used space
---------- ----------
665959.513    744.375

The bytes column in both tables are different. The output I'm getting above should come in a single row like this:

Total Size    Used space
----------   -----------
90.9035645   74.4375

How should I modify the above query? Please help.

tablespace oracle sql
1 Answers

There are lots ways to do this, I'm thinking of using imaginary column to join your second table.

SELECT t1.totalspace as "Total Space", t2.usedspace as "Used Space"  
FROM 
    (SELECT sum(bytes/1024/1024/1024) AS totalspace, 1 as col
    FROM dba_data_files) t1
LEFT JOIN
    (SELECT sum(bytes/1024/1024/1024) AS usedspace, 1 as col
    FROM dba_data_files) t2 ON t2.col = t1.col
user_64290720
Related