SQL-Query 1

[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

 

[HimanB]

Hi,
Make use of following query:

SELECT COL1, COL2,'1' TO_COL5,sum(DECODE(COL5,1,1,0)) TOTAL FROM T4
GROUP BY COL1, COL2
UNION
SELECT COL1, COL2,'<>1' TO_COL5,SUM(DECODE(COL5,1,0,1)) TOTAL 
FROM T4
GROUP BY COL1, COL2;

Regards
Himanshu