sqloracle-databasetablespace

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


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.


Solution

  • 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