CODEWITHSUNDEEP
sqloracle-database
Rohan Lopes
Rohan Lopes

Reputation: 165

SQL get row value where count of rows for that row value matches with of the column of that row-value

I have table in oracle db as follow.

ID  | TOTAL_IDS
----+----------
A1  + 2
A1  + 2
B1  + 1
C1  + 3
C1  + 3
C1  + 3
D1  + 2

I want list of IDs where count of distinct IDs matches with TOTAL_IDs. For eg. count of ID A1 is 2 and it matches TOTAL_IDS column.

So my query should return A1,B1,C1.

Upvotes: 0

Views: 57

Answers (4)

Hong Van Vit
Hong Van Vit

Reputation: 2986

try: 

SELECT ID, TOTAL_IDS
FROM tb
GROUP BY ID, TOTAL_IDS
HAVING COUNT(ID) = TOTAL_IDS

Upvotes: 0

Pankaj_Dwivedi
Pankaj_Dwivedi

Reputation: 580

select ID,count(ID) from table group by ID having count(ID)=count(TOTAL_IDS);

Upvotes: 0

Indent
Indent

Reputation: 4967

Try Having clause : http://sqlfiddle.com/#!4/06eed6/9
min=max ==> all row have the same value (group by IDS)
min=count ==> your expected citeria

select
    IDS
from
    your_table
group by
    IDS
having
    min(TOTAL_IDS) = count(*) and
    max(TOTAL_IDS) = min(TOTAL_IDS)

return :

| IDS |
|-----|
|  A1 |
|  B1 |
|  C1 |

Upvotes: 4

jarlh
jarlh

Reputation: 44776

select distinct id
from tablename t1
where TOTAL_IDS = (select count(*) from tablename t2
                   where t2.id = t1.id)

Will not detect inconsistent table data, e.g. if ('D1', 4) or ('D2',null) is added to the table.

Upvotes: 1

Related Questions