Re: wrong output from Join

On 22 Jul 2005 09:04:05 -0700, "Jagjeet Singh"
<jagjeet_malhi@xxxxxxxxxxx> wrote:

>Hi,
>
> I need to calculate the sum of tablespace's size and its free size.
>
>Select a.tablespace_name,sum(a.bytes) total_mb,sum(b.bytes) Free_mb
>from dba_data_files a , dba_free_space b
>where a.tablespace_name = b.tablespace_name
>group by a.tablespace_name
>/
>
>This query is givien wrong output.
>
>Can anyone is explain it why is it giving wrong output.
>
>Thanks,
>Js

One record from dba_data_files will join to all corresponding records
of dba_free_space, so to all corresponding free segments.

This will work
select used.tablespace_name, used.bytes, free.bytes
from
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) used,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name
) free


--
Sybrand Bakker, Senior Oracle DBA
.

Relevant Pages

  • Please help total working hours/min??
    ... using Access how would i go about creating a query that will sum up the ... I would be realy greatful if someone could help me with this problem. ... Prev by Date: ...
    (microsoft.public.access.reports)
  • Using Format on summarizing
    ... FROM table1; ... But if i want to calculate the sum of this field with the specific format.... ... this means...the query becomes: ... Prev by Date: ...
    (microsoft.public.access.queries)
  • Re: Count on a yes/no field
    ... >I am using Access 2002 and I have a query that I would like to count a ... that will give you -103 as a sum. ... The minus sign will ... Prev by Date: ...
    (microsoft.public.access.queries)
  • Multiplying Grouped Columns
    ... I have a query, which selects three columns from an inner join. ... columns are SUM and the first one is GROUP BY. ... Prev by Date: ...
    (microsoft.public.sqlserver.dts)
  • Re: simple SQL query question
    ... >Ok I have this simple query.. ... >order of the inclause. ... Sybrand Bakker, Senior Oracle DBA ... Prev by Date: ...
    (comp.databases.oracle.server)