Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: brain F*rt question
Ron Rogers wrote:
>
> Stephane ,
> Thanks for the reply.
> I tried using a join function and I got the sum(data) I wanted but it listed the
>date twice, Once for each table select. In your reply you mentioned a percentage
>calculation problem.??? I think the sums are wrong because they get summed for each
>occurance of an enter in the table.
> What do you mean by an in-line view? I tried creating views to gather the data
>needed but have problems selecting a particular retnbr , saledate range, and grouping
>by saledate across two tables.
> Thanks,
> Ron
>
> Ron,
> Looks to me like the classical percentage computation problem. Sums
> are wrong because applied to the result of the join. Compute your sums
> in an in-line view in the FROM clause.
> --
Have you ever tried to list how full your tablespaces are ? In percentage ? This is what I meant. Basically to do this you need to sum up the blocks on dba_data_files (to see how much has been allocated) and on say dba_segments (to see how much is used). Double sum, and if you join and try to compute a percentage, you get wrong numbers. This is how it is properly done :
select x.tablespace_name, nvl(round(100 * sum(s.blocks) / x.blocks, 2),
0) PCT_FULL
from dba_segments s,
(select tablespace_name, sum(blocks) blocks <-- This is an in-line view
from dba_data_files group by tablespace_name) x
I may have the column names wrong (and possibly the view names :-)) but it's the idea. I think that in spirit your problem is very close.
-- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: sfaroult_at_oriole.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Dec 19 2001 - 15:30:05 CST