Oracle aggregated query

Question

ColA ColB ColC ColD
data1 rata1 T Test1
data1 rata3 F Test2
data1 rata2 T Test1
data2 rata1 T Test1
data2 rata3 T Test1
data3 rata4 T Test1

Have four columns of type Varchar. What i wish to determine is:-
1. For each unique value of ColB(rata1, rata2 etc), find corresponding value of ColA and other two columns (ColC, ColD) where that specific value of ColA has ColC value equal to 'T' 0 or 1 times.

rata2 data1 T Test1
rata3 data2 T Test1 
rata4 data3 T Test1

2. In the above query when ColC was equal to 'T' 0 or 1 times, find other rows for which ColA has ColC equal to 'F' for that specific value of ColB.

rata3 data1 F Test2

@Isaac: Please see the other response and my comment on the same. — John C
– John C, Commented Jul 11, 2011 at 23:14

Hector Sanchez · Accepted Answer · 2011-07-11 23:27:52Z

1

Didn't understand your question at all. I'll correct the answer if you say something else. but as far I understood you want something like this?

Edit: I made the same table that you gave us in a DB. And this worked fine. Try it and tell me if it works

SELECT COLUMNB,MAX(COLUMNA), MAX(ColumnC), MAX(COLUMND)
FROM DUMMY
WHERE ColumnC = 'T'
GROUP BY COLUMNB
HAVING SUM(CASE WHEN COLumnC = 'T' THEN 1 ELSE 0 END) <= 1

UNION ALL
SELECT * 
FROM  DUMMY
WHERE COLumnC = 'F'

Edit 2: What about this?

WITH B
AS(
SELECT A.COLUMNB, MAX(A.COLUMNA) AS COLUMNA, MAX(A.ColumnC) AS COLUMNC, MAX(A.COLUMND) AS COLUMND
FROM DUMMY A
GROUP BY A.COLUMNB
HAVING SUM(CASE WHEN A.COLumnC = 'T' THEN 1 ELSE 0 END) <= 1)


SELECT * 
FROM B 

UNION ALL
SELECT D.COLUMNB, D.ColumnA, D.ColumnC, D.ColumnD
FROM B, DUMMY D
WHERE  B.ColumnB = D.COLUMNB
AND D.ColumnC = 'F'

edited Jul 11, 2011 at 23:27

answered Jul 11, 2011 at 22:40

Hector Sanchez

2,3274 gold badges26 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John C Over a year ago

Thanks a lot for your valuable suggestions. They were very helpful. The first query works well wherein for each unique value of ColB you retrieved values where that specific value of ColA has ColC value equal to 'T' 0 or 1 times. Now, in this scenario for query 2- if that specific value of ColA has ColC equal to 'T' 0 or 1 times find those rows for which ColA has ColC equal to 'F' for that specific value of ColB. Please ask questions in case something is not clear.

Collectives™ on Stack Overflow

Oracle aggregated query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related