CODEWITHSUNDEEP
sqloracle-database
ntdrv
ntdrv

Reputation: 31

How to subtract values using SQL

Could you tell me how can I subtract values?

SQL> 

select SUM(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='UNDOTBS1'

2 ;

SUM(BYTES/1024/1024)
--------------------
            7000

SQL> 

select SUM(BYTES/1024/1024) from DBA_UNDO_EXTENTS where STATUS LIKE 'ACTIVE';

SUM(BYTES/1024/1024)
--------------------
               8

I need to get a value 7000 - 8

When I do

select SUM(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='UNDOTBS1'
minus
select SUM(BYTES/1024/1024) from DBA_UNDO_EXTENTS where STATUS='ACTIVE';

I get result only from the first select.

Upvotes: 3

Views: 4225

Answers (4)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

Another way of doing it: 

select (SUM(u.bytes) - SUM(a.BYTES)) /1024/1024
from dba_data_files u
   cross join DBA_UNDO_EXTENTS a
where u.TABLESPACE_NAME='UNDOTBS1'
   and a.STATUS='ACTIVE';

Upvotes: 0

frlan
frlan

Reputation: 7260

Might work:

SELECT sum_a-sum_b as result
FROM
    (SELECT SUM(bytes/1024/1024) as sum_a from dba_data_files where TABLESPACE_NAME='UNDOTBS1') as a, 
    (select SUM(BYTES/1024/1024) as sum_b from DBA_UNDO_EXTENTS where STATUS LIKE 'ACTIVE') as b

Upvotes: 1

Sam DeHaan
Sam DeHaan

Reputation: 10325

The syntax of 

SELECT ...
minus
SELECT...

is using a set mathematics approach. It is subtracting the set [8] from the set [7000], leaving it with the set [7000]. Other answers have given you options for resolving this and getting the information you want.

Upvotes: 0

StuartLC
StuartLC

Reputation: 107237

Use the dual pseudo table here and evaluate the sums as scalars:

SELECT 
   (SELECT SUM(bytes/1024/1024) as sum_a 
    from dba_data_files where TABLESPACE_NAME='UNDOTBS1')
 - (select SUM(BYTES/1024/1024) as sum_b 
    from DBA_UNDO_EXTENTS where STATUS LIKE 'ACTIVE') as Difference
FROM
    dual;

Upvotes: 6

Related Questions