CODEWITHSUNDEEP
sqloracle-database
kenny
kenny

Reputation: 2040

Groupby where all children match a condition

I have a table:

+--+---------+------+
|ID|Parent_ID|Status|
+--+---------+------+
|1 |0        |      |
+--+---------+------+
|2 |0        |      |
+--+---------+------+
|3 |1        |A     |
+--+---------+------+
|4 |1        |B     |
+--+---------+------+
|5 |1        |C     |
+--+---------+------+
|6 |2        |A     |
+--+---------+------+
|7 |2        |B     |
+--+---------+------+

I want to get the parent ID, and the count of children BUT take groups where all children have status A or B

so based on the above table I want to see only: 2,2

select parent_id,count(1) from MYTABLE
where parent_id != 0
group by parent_id
HAVING (status) IN
   ('A','B')

Upvotes: 1

Views: 72

Answers (2)

Justin Iurman
Justin Iurman

Reputation: 19016

Try this:

SELECT Parent_ID, COUNT(ID) 
FROM Table
WHERE Parent_ID <> 0 
      AND Parent_ID NOT IN
      (
         SELECT DISTINCT(Parent_ID) FROM Table WHERE Status NOT IN ('A', 'B')
      )
GROUP BY Parent_ID

Upvotes: 2

juergen d
juergen d

Reputation: 204884

Select only those having zero status other than A or B

select parent_id, count(1) 
from MYTABLE
where parent_id != 0
group by parent_id
HAVING sum(case when status NOT IN ('A','B') then 1 else 0 end) = 0

Upvotes: 2

Related Questions