This discussion is archived
12 Replies Latest reply: Dec 12, 2012 11:27 AM by Rahul_India RSS

sum of columns in union query

750281 Newbie
Currently Being Moderated
hi, i am using oracle 10g database..

how to get the sum of column in union query ?
     select * from (select 100 records from dual), (select 50 available from dual)
union
select * from (select 200 records from dual), (select 150 available from dual)


display should be like 

                 records         available
                  100               50
                  200               150
 total            300               200
thanks ...
  • 1. Re: sum of columns in union query
    Nicosa Expert
    Currently Being Moderated
    Hi,

    This maybe ?
    SQL> l
      1  with t(c1,c2) as (
      2       select 100, 50 from dual
      3       union all
      4       select 200,150 from dual
      5  )
      6  select sum(c1) sc1,sum(c2) sc2
      7  from t
      8* group by grouping sets((),(c1,c2))
    SQL> /
    
           SC1       SC2
    ---------- ----------
           100        50
           200       150
           300       200
  • 2. Re: sum of columns in union query
    Solomon Yakobson Guru
    Currently Being Moderated
    select  sum(records) records,
            sum(available) available
      from  (
              select * from (select 100 records from dual), (select 50 available from dual)
             union
              select * from (select 200 records from dual), (select 150 available from dual)
            )
      group by rollup(rownum)
    /
    
       RECORDS  AVAILABLE
    ---------- ----------
           100         50
           200        150
           300        200
    
    SQL> 
    SY.
  • 3. Re: sum of columns in union query
    Peter vd Zwan Expert
    Currently Being Moderated
    hi,

    try this:
    with a as 
    (
    select * from (select 100 records from dual), (select 50 available from dual)
    union
    select * from (select 200 records from dual), (select 150 available from dual)
    )
    select
      case when grouping(records) = 0 then null else 'total' end tot
      ,sum(records)   records
      ,sum(available) available
    from
      a
    group by
    rollup (records)
    ;
    TOT   RECORDS AVAILABLE
    ----- ------- ---------
              100        50 
              200       150 
    total     300       200 
    Regards,

    Peter
  • 4. Re: sum of columns in union query
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    You can use GROUP BY ROLLUP or GROUP BY GROUPING SETS for that. You have to do the UNION first, in a sub-query, like this:
    WITH     union_results AS
    (
         select * from (select 100 records from dual), (select 50 available from dual)
        union
            select * from (select 200 records from dual), (select 150 available from dual)
    )
    SELECT       CASE
               WHEN  GROUPING (ROWNUM) = 1
               THEN  'Total'
           END               AS label
    ,       SUM (records)          AS records
    ,       SUM (available)      AS avaialable
    FROM       union_results
    GROUP BY  ROLLUP (ROWNUM)
    ;
    Output:
    LABEL    RECORDS AVAIALABLE
    ----- ---------- ----------
                 100         50
                 200        150
    Total        300        200
    By the way, a simpler way of generating the same data is:
    SELECT 100 AS records, 50 AS available FROM dual     UNION ALL
    SELECT 200,               150               FROM dual
    You can SELECT as many columns as you need to in a single query; there's no need for a cross-join.
  • 5. Re: sum of columns in union query
    Solomon Yakobson Guru
    Currently Being Moderated
    Peter vd Zwan wrote:
    try this:
    Grouping by records will not produce correct results:
    SQL> with a as 
      2  (
      3  select * from (select 100 records from dual), (select 50 available from dual)
      4  union
      5  select * from (select 100 records from dual), (select 100 available from dual)
      6  union
      7  select * from (select 200 records from dual), (select 150 available from dual)
      8  )
      9  select
     10    case when grouping(records) = 0 then null else 'total' end tot
     11    ,sum(records)   records
     12    ,sum(available) available
     13  from
     14    a
     15  group by
     16  rollup (records)
     17  ;
    
    TOT      RECORDS  AVAILABLE
    ----- ---------- ----------
                 200        150
                 200        150
    total        400        300
    
    SQL> select  case grouping(rownum)
      2     when 1 then 'Total'
      3   end display,
      4          sum(records) records,
      5          sum(available) available
      6    from  (
      7            select * from (select 100 records from dual), (select 50 available from dual)
      8           union
      9            select * from (select 100 records from dual), (select 100 available from dual)
     10           union
     11            select * from (select 200 records from dual), (select 150 available from dual)
     12          )
     13    group by rollup(rownum)
     14  /
    
    DISPL    RECORDS  AVAILABLE
    ----- ---------- ----------
                 100         50
                 100        100
                 200        150
    Total        400        300
    
    SQL> 
    SY.
  • 6. Re: sum of columns in union query
    750281 Newbie
    Currently Being Moderated
    i want to create a view using this query and need a sum of the columns.. am i need to write with clause while creating view?
  • 7. Re: sum of columns in union query
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Hi FRNzzz!! wrote:
    i want to create a view using this query and need a sum of the columns.. am i need to write with clause while creating view?
    It depends on your data and the results you want.


    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved.
    Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
    Simplify the problem as much as possible. Remove all tables and columns that play no role in this problem.
    Always say which version of Oracle you're using (for example, 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 8. Re: sum of columns in union query
    Peter vd Zwan Expert
    Currently Being Moderated
    Thanks,

    I learn everyday.
  • 9. Re: sum of columns in union query
    750281 Newbie
    Currently Being Moderated
    thanks. i found new thing to learn from you all expert.

    just clear some doubts
    with a as 
      2  (
      3  select * from (select 100 records from dual), (select 50 available from dual)
      4  union
      5  select * from (select 100 records from dual), (select 100 available from dual)
      6  union
      7  select * from (select 200 records from dual), (select 150 available from dual)
      8  )
      9  select
     10    case when grouping(records) = 0 then null else 'total' end tot
     11    ,sum(records)   records
     12    ,sum(available) available
     13  from
     14    a
     15  group by
     16  rollup (records)
    1. grouping(rownum) not understood .
    2. how to create a view using this query ? confused bit about with clause .
    am i need to write create or replace view abc as with a as ...?
  • 10. Re: sum of columns in union query
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Hi FRNzzz!! wrote:
    thanks. i found new thing to learn from you all expert.

    just clear some doubts
    with a as 
    2  (
    3  select * from (select 100 records from dual), (select 50 available from dual)
    4  union
    5  select * from (select 100 records from dual), (select 100 available from dual)
    6  union
    7  select * from (select 200 records from dual), (select 150 available from dual)
    8  )
    9  select
    10    case when grouping(records) = 0 then null else 'total' end tot
    11    ,sum(records)   records
    12    ,sum(available) available
    13  from
    14    a
    15  group by
    16  rollup (records)
    1. grouping(rownum) not understood .
    Like all built-in functions, GROUPING is described in the SQL Language manual
    2. how to create a view using this query ? confused bit about with clause .
    am i need to write create or replace view abc as with a as ...?
    Exactly! If you need a WITH clause, that's where it goes. If you have problems, post a complete test script that people can run to re-create the problem and test their ideas.
  • 11. Re: sum of columns in union query
    Sven W. Guru
    Currently Being Moderated
    Hi FRNzzz!! wrote:
    thanks. i found new thing to learn from you all expert.

    just clear some doubts
    1. grouping(rownum) not understood .
    2. how to create a view using this query ? confused bit about with clause .
    am i need to write create or replace view abc as with a as ...?
    That is possible. What happend when you tried it?

    You can also put the part from the with clause as a inline view into the from clause.
    select
       case when grouping(records) = 0 then null else 'total' end tot
       ,sum(records)   records
       ,sum(available) available
      from ( select ... 
               union ALL  
               select ...
             )
    group by rollup (records)
    Note that you MUST use union all or you will get wrong results depending on the data situation.
  • 12. Re: sum of columns in union query
    Rahul_India Journeyer
    Currently Being Moderated
    Hi FRNzzz!! wrote:
    hi, i am using oracle 10g database..

    how to get the sum of column in union query ?
    select * from (select 100 records from dual), (select 50 available from dual)
    union                                  
    select * from (select 200 records from dual), (select 150 available from dual)
    
    
    display should be like 
    
    records         available
    100               50
    200               150
    total            300               200
    thanks ...
    a very easy way
    WITH T AS
    ( SELECT 100 RECORD,50 AVAILABLE FROM DUAL UNION ALL
    SELECT 200 RECORD,150 AVAILABLE FROM DUAL
    ) 
    SELECT SUM(RECORD) record,SUM(AVAILABLE) available FROM T
    UNION    -- UNION ALL depending on your data set
    SELECT * FROM T

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points