CODEWITHSUNDEEP
sqloracle10g
user779608
user779608

Reputation: 167

Selecting rows with duplicate values grouped

I have two tables that look something like this

Table Dog:

PK, color
1,  red
2,  yellow
3,  red
4,  red
5,  yellow

The dogs have toys.

Table toys

PK, FK, name
1,  2,  bowser
2,  2,  oscar
3,  3,  greg
4,  4,  alp
5,  4,  hanson
6,  5,  omar
7,  5,  herm

I need a query that selects the count of all yellow dogs that have more than one toy.

I was thinking somehting like:

Select count(*)
from toys t, dogs d
where t.fk = d.pk
and d.color = 'yellow'
group by t.fk    
having count(t.fk) > 1;

It should return 2. but it comes back with mutiple rows

Upvotes: 0

Views: 99

Answers (1)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

select count(*)
from (
    select FK
    from Toys t
    inner join Dogs d on t.FK = d.PK
    where d."color" = 'yellow'
    group by FK
    having count(*) > 1
)

SQL Fiddle Example

Upvotes: 1

Related Questions