CODEWITHSUNDEEP
oracle-databaseaggregate-functions
Rich P
Rich P

Reputation: 202

How to write a query to select only groups where certain values are present in all rows?

I need to write a query for Oracle 11g to select only the groups which are highlighted in the image here. Specifically, there are 3 columns -- A, B, and C. Group 1 contains a null value in column C, group 2 contains several nulls in column C.Group 3 does not contain any null values in column C. Group 4 yes contains some null values in column C, and Group 5 does NOT contain any null values in column C.

enter image description here

I need to selects only the groups of rows that do NOT contain any null values in column C -- that would be group 3 rows and group 5 rows. I only need the group number, like return only a 3 and a 5 What SQL functions can I use to write this query? What does this query look like?

something like (pseudo code)

select colA, count(*) As cnt From tblX join to itself on something
Where ...something ... 
Group By colA

(or we could skip the grouping and just select 3 and 5 or the three 3's and the three 5's)

Upvotes: 1

Views: 1444

Answers (2)

mustaccio
mustaccio

Reputation: 18980

The aggregate function COUNT(colname) would only count non-NULL values, so something like this might work:

select A from yourtable
group by A
having count(A) = count(C)

assuming A is never NULL.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

You can use a not in clause and subquery

select colA, count(*) As cnt 
From tblX 
Where colA not in (select distinct colA from tblx where colC is  null)
Group By colA

Upvotes: 1

Related Questions