GROUP and ROLLUP

[hudo]

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

[martas]

I'm not sure if I undeerstood the problem correctly, may be decode might help. Try this :

SELECT COL1, COL2 ,decode(COL5, 1, 'OK', 'Fail') "RESULT", COUNT(*) "TOTAL"
FROM COL_TABLE
GROUP BY COL1,COL2,decode(COL5, 1, 'OK', 'Fail');

[hudo]

Hello,
thanks for your answer. I solved the problem with the followinng SELECT-Statement:

SELECT col1, col2, ok, bad,
ok/TOTAL*100 ok_percent, bad/TOTAL*100 bad_percent
FROM
(SELECT col1, col2,
SUM(DECODE(col5, 1, 1, 0)) ok,
SUM(DECODE(col5, 1, 0, 1)) bad,
count(*) TOTAL
FROM col_table
GROUP BY col1, col2);