Forum Stats

  • 3,757,567 Users
  • 2,251,246 Discussions
  • 7,869,867 Comments

Discussions

Matrix

585321
585321 Member Posts: 522
edited May 12, 2009 9:27AM in SQL & PL/SQL
A B C D E F G total1
AA 1 2 3 4 5 6 7 28
BB 2 2 3 4 5 6 7 29
CC 3 4 5 6 7 8 7 40
DD 4 3 4 5 6 7 7 36
total2 10 11 15 19 23 27 28 133
How do I write a sql (oracle and t-sql) which would help me sum up the rows and columns . Thanks
«1

Answers

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    edited May 8, 2009 1:51PM
    Is this your sample input or output? It looks like they're summed up already.

    Please post proper create table and insert statements.
  • 585321
    585321 Member Posts: 522
    Thats a sample and I need total1 and total2 columns
    Thanks
  • 666352
    666352 Member Posts: 1,442
    edited May 8, 2009 2:21PM
    With model caluse (10g)
    SELECT  a,b,c,d,e,f,g,h,total1
        FROM your table -- put your table 
    model 
    dimension by (a)
    measures(  b,c,d,e,f,g,h,NVL (b, 0)
             + NVL (c, 0)
             + NVL (d, 0)
             + NVL (e, 0)
             + NVL (f, 0)
             + NVL (g, 0)
             + NVL (h, 0) total1)
    (b['Total2']=sum(b)[any],
    c['Total2']=sum(c)[any],
    d['Total2']=sum(d)[any],
    e['Total2']=sum(e)[any],
    f['Total2']=sum(f)[any],
    g['Total2']=sum(g)[any],
    h['Total2']=sum(h)[any],
    total1['Total2']=sum(Total1)[any])
    Demo
    set linesize 200
    drop table t;
    create table t ( a varchar2(10),  b number,  c number , d number, e number,  f number,  g number, h number);
    
    insert into t
         SELECT 'AA' a, 1 b, 2 c, 3 d, 4 e, 5 f, 6 g, 7 h
            FROM DUAL
          UNION ALL
          SELECT 'BB', 2, 2, 3, 4, 5, 6, 7
            FROM DUAL
          UNION ALL
          SELECT 'CC', 3, 4, 5, 6, 7, 8, 7
            FROM DUAL
          UNION ALL
          SELECT 'DD', 4, 3, 4, 5, 6, 7, 7
            FROM DUAL;
            
    SELECT  a,b,c,d,e,f,g,h,total1
        FROM t
    model 
    dimension by (a)
    measures(  b,c,d,e,f,g,h,NVL (b, 0)
             + NVL (c, 0)
             + NVL (d, 0)
             + NVL (e, 0)
             + NVL (f, 0)
             + NVL (g, 0)
             + NVL (h, 0) total1)
    (b['Total2']=sum(b)[any],
    c['Total2']=sum(c)[any],
    d['Total2']=sum(d)[any],
    e['Total2']=sum(e)[any],
    f['Total2']=sum(f)[any],
    g['Total2']=sum(g)[any],
    h['Total2']=sum(h)[any],
    Total1['Total2']=sum(Total1)[any]);
    
    Table dropped.
    Table created.
    4 rows created.
    
    A                   B          C          D          E          F          G          H     TOTAL1
    ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
    AA                  1          2          3          4          5          6          7         28
    BB                  2          2          3          4          5          6          7         29
    CC                  3          4          5          6          7          8          7         40
    DD                  4          3          4          5          6          7          7         36
    Total2             10         11         15         19         23         27         28        133
    
    
    5 rows selected.
    Edited by: Salim Chelabi on 2009-05-08 11:21
  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    Thats a sample
    <shrugs>
  • 666352
    666352 Member Posts: 1,442
    edited May 8, 2009 2:44PM
    Solution with GROUP BY ROLLUP, without model clause :) .

    Regards Salim.

    Regards Salim.
    /* Formatted on 2009/05/08 14:41 (Formatter Plus v4.8.8) */
    SELECT   NVL (a, 'Total2') a, NVL (b, SUM (b)) b, NVL (c, SUM (c)) c,
             NVL (d, SUM (d)) d, NVL (e, SUM (e)) e, NVL (f, SUM (f)) f,
             NVL (g, SUM (g)) g, NVL (h, SUM (h)) h,
             NVL (  NVL (b, 0)
                  + NVL (c, 0)
                  + NVL (d, 0)
                  + NVL (e, 0)
                  + NVL (f, 0)
                  + NVL (g, 0)
                  + NVL (h, 0),
                  SUM (  NVL (b, 0)
                       + NVL (c, 0)
                       + NVL (d, 0)
                       + NVL (e, 0)
                       + NVL (f, 0)
                       + NVL (g, 0)
                       + NVL (h, 0)
                      )
                 ) total1
        FROM t
    GROUP BY ROLLUP ((a,
                      b,
                      c,
                      d,
                      e,
                      f,
                      g,
                      h,
                        NVL (b, 0)
                      + NVL (c, 0)
                      + NVL (d, 0)
                      + NVL (e, 0)
                      + NVL (f, 0)
                      + NVL (g, 0)
                      + NVL (h, 0)
                     ));
    
    A                                         B          C          D          E          F          G          H     TOTAL1
    -------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
    AA                                        1          2          3          4          5          6          7         28
    BB                                        2          2          3          4          5          6          7         29
    CC                                        3          4          5          6          7          8          7         40
    DD                                        4          3          4          5          6          7          7         36
    Total2                                   10         11         15         19         23         27         28        133
    
    
    5 rows selected.
    Edited by: Salim Chelabi on 2009-05-08 11:44
  • SanjayRs
    SanjayRs Member Posts: 3,024
    You can find the sql how to get the, if you search on web or this forums.

    To write a sql which runs on both Oracle and Sql Server you will have to write a ANSI SQL 92 compliant sql.

    ANSI GROUP BY CUBE ROLLUP syntax by is supported SQL Server 2008 onwards.


    SS
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Or (assuming no null values - otherwise wrap the columns with NVL):
    select   t.*, b + c + d + e + f + g + h total1 from t
    union all
    select   'total2',
             sum (b),
             sum (c),
             sum (d),
             sum (e),
             sum (f),
             sum (g),
             sum (h),
             sum (b + c + d + e + f + g + h) total1
      from   t
    
    A               B          C          D          E          F          G          H     TOTAL1
    ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
    AA              1          2          3          4          5          6          7         28
    BB              2          2          3          4          5          6          7         29
    CC              3          4          5          6          7          8          7         40
    DD              4          3          4          5          6          7          7         36
    total2         10         11         15         19         23         27         28        133
    
    5 rows selected.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited May 11, 2009 6:03AM
    Yesterday, I learned nice rollup usage from below thread :-)
    899486

    Although I will research,
    I want to know whether this rollup usage is documented or not.
    col b for 99
    col c for 99
    col d for 99
    col e for 99
    col f for 99
    col g for 99
    col h for 99
    
    with T as (
    SELECT 'AA' a,1 b, 2 c,3 d,4 e,5 f,6 g,7 h FROM DUAL UNION
    SELECT 'BB',2,2,3,4,5,6,7 FROM DUAL UNION
    SELECT 'CC',3,4,5,6,7,8,7 FROM DUAL UNION
    SELECT 'DD',4,3,4,5,6,7,7 FROM DUAL)
    select decode(grouping(a),0,a,'Total2') as a,
    sum(b) as b,sum(c) as c,sum(d) as d,sum(e) as e,
    sum(f) as f,sum(g) as g,sum(h) as h,
    sum(b+c+d+e+f+g+h) as TOTAL1
      from t
    group by rollup((a,b,c,d,e,f,g,h));
    
    A        B   C   D   E   F   G   H  TOTAL1
    ------  --  --  --  --  --  --  --  ------
    AA       1   2   3   4   5   6   7      28
    BB       2   2   3   4   5   6   7      29
    CC       3   4   5   6   7   8   7      40
    DD       4   3   4   5   6   7   7      36
    Total2  10  11  15  19  23  27  28     133
  • 666352
    666352 Member Posts: 1,442
    Hi Aketi Jyuuzou ,

    Sincerely I learned by Frank. i don't know if it's documented or not.

    I not found documentation for that.

    Thanks Frank.

    You must put SUM (nvl(b,0) + nvl(c,0) + nvl(d,0) + nvl(e ,0)+nvl( f,0) + nvl(g,0)+ nvl(h,0)) if one of all columns can be null.
    WITH t AS
         (SELECT 'AA' a, 1 b, 2 c, 3 d, 4 e, 5 f, 6 g, 7 h
            FROM DUAL
          UNION
          SELECT 'BB', NULL, 2, 3, 4, 5, 6, 7
            FROM DUAL
          UNION
          SELECT 'CC', 3, NULL, 5, 6, 7, 8, 7
            FROM DUAL
          UNION
          SELECT 'DD', 4, 3, 4, NULL, 6, 7, 7
            FROM DUAL)
    SELECT   DECODE (GROUPING (a), 0, a, 'Total2') AS a, SUM (b) AS b,
             SUM (c) AS c, SUM (d) AS d, SUM (e) AS e, SUM (f) AS f, SUM (g) AS g,
             SUM (h) AS h, SUM (b + c + d + e + f + g + h) AS total1
        FROM t
    GROUP BY ROLLUP ((a, b, c, d, e, f, g, h));
    
    
    A        B   C   D   E   F   G   H     TOTAL1
    ------ --- --- --- --- --- --- --- ----------
    AA       1   2   3   4   5   6   7         28
    BB           2   3   4   5   6   7           
    CC       3       5   6   7   8   7           
    DD       4   3   4       6   7   7           
    Total2   8   7  15  14  23  27  28         28
    
    
    5 rows selected.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    Today I researched Oracle ducuments.
    Then I found that this rollup usage is documented B-)

    http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19217-02/aggreg.htm#sthref1653

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/aggreg.htm#sthref1650

    In documents, above rollup usage is called Composite Columns
This discussion has been closed.