1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Calculating the results of a query or Sum + Sum

Discussion in 'SQL PL/SQL' started by Izzame, May 15, 2012.

  1. Izzame

    Izzame Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Im trying to work out how to do a 2 queries sum from where and have the result of the calcualtion so for e.g

    sum from where + sum from where

    How do you do it? Ive searched everywhere for the answer?

    thankyou
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That depends upon which version you're using and if you want the result usable by some other process or just as an output. From 10g onwards you can use subquery factoring (the WITH clause) to generate these sums as individual entities and then, in a master query, generate your overall total:

    Code (SQL):
    SQL> WITH a AS(
    2 SELECT SUM(bytes) ttl_free FROM dba_free_space
    3 ),
    4 b AS (
    5 SELECT SUM(bytes) ttl_tmp FROM v$tempfile
    6 )
    7 SELECT ttl_free + ttl_tmp
    8 FROM a, b;
     
    TTL_FREE+TTL_TMP
    ----------------
    48260841472
     
    SQL>
    SQL>
    SQL> break ON report skip 1
    SQL> compute SUM OF ttl ON report
    SQL>
    SQL> SELECT SUM(bytes) ttl FROM dba_free_space
    2 UNION
    3 SELECT SUM(bytes) ttl FROM v$tempfile;
     
    TTL
    ---------------
    6714032128
    41546809344
    ---------------
    48260841472
     
    SQL>
    SQL> DECLARE
    2 ttlf NUMBER;
    3 ttlt NUMBER;
    4 total NUMBER;
    5
    6 BEGIN
    7 SELECT SUM(bytes) INTO ttlf FROM dba_free_space;
    8 SELECT SUM(bytes) INTO ttlt FROM v$tempfile;
    9
    10 total:=ttlt+ttlf;
    11
    12 dbms_output.put_line('Total space: '||total);
    13
    14 END;
    15 /
     
    Total SPACE: 48260841472
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
     
    Note also the PL/SQL code and using SQL*Plus commands to generate a sum at the end of the query block.
     
  3. Izzame

    Izzame Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thats great and works really well,
    I knew how to do it programmatically but wanted to do it in sql alone.

    thankyou really helpful
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    And here is the query without WITH clause that runs in earlier versions as well.

    Code (SQL):

    SELECT x + y FROM
         ( SELECT SUM(bytes)x FROM dba_free_space  ) d  ,
         ( SELECT SUM(bytes)y FROM v$tempfile) t;