Results 1 to 2 of 2

Thread: RollUP

  1. #1
    Join Date
    Aug 2003
    Posts
    26

    Unanswered: RollUP

    Hello,

    I got the following table-description:

    CREATE TABLE COL_TABLE
    (COL1 VARCHAR2(10),
    COL2 VARCHAR2(10),
    COL3 VARCHAR2(10),
    COL4 NUMBER(4),
    COL5 NUMBER(4));

    with the following entries:

    COL1 COL2 COL3 COL4 COL5
    AAA STA1 1 10 1
    AAA STA1 1 11 60
    AAA STA1 1 12 1
    AAA STA1 2 19 50
    AAA STA1 2 9 1
    AAA STA2 1 9 1
    AAA STA2 1 9 1
    AAA STA2 3 9 40
    AAA STA3 2 7 1
    AAA STA3 2 17 1
    AAA STA3 2 12 1

    and I'd like to have the as a result from an sql-statement:

    COL1 COL2 TO_COL5 TOTAL
    AAA STA1 1 3
    AAA STA1 <>1 2
    AAA STA2 1 2
    AAA STA2 <>1 1
    AAA STA3 1 3


    This looks similar to the following sql-query:

    SELECT COL1, COL2 ,COL5
    ,COUNT(*) "TOTAL"
    FROM COL_TABLE
    GROUP BY ROLLUP(COL1,COL2,COL5);

    but first: the superordinated rows are deleted
    second: the entries in COL5 are divided in two groups: entry = 1 ; entry <>1 (for example software-programms exit-code 1 means everything ok, while a code <>1 indicates an error).
    So I want to sum up all "good" programm-exit and all "erroneous" programm-exits.


    Thanks in advance

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    something like this?

    SELECT COL1, COL2 ,COL5, decode(COL5,1,'1','<>1') to_col5, COUNT(*) "TOTAL"
    FROM COL_TABLE
    GROUP BY COL1, COL2, decode(COL5,1,'1','<>1');

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •