12 Replies Latest reply: Dec 12, 2012 1:27 PM by Rahul_India RSS

    sum of columns in union query

    750281
      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-Oracle
          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
            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
              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
                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
                  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
                    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
                      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
                        Thanks,

                        I learn everyday.
                        • 9. Re: sum of columns in union query
                          750281
                          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
                            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.
                              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
                                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