Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Count / group by

Count / group by

Count / group by

Hi there,
Another plain SQL question.  I have data that holds entity relationships across years. From it I'd like to know which values of Entity2 have only ever been related to a single Entity1 regardless of the year.

Data looks like the following...  

Entity1    Entity2    Year    
443            444      1998    
4685           4684     1999    
4689           4684     1999    
8007         16355    2009    
8007         16355    2010    
8007        16355    2011    
8007        16355    2012    
363914      37491    2012    
146231      37491    2011    
363914      37491    2008    
363914      37491    2009    
363914      37491    2011    
146231      37491    2012    
363914      37491    2006    
363914      37491    2010    

I expect 444, 16355 in the result but I just can not figure out how to write the select.
Any insight is much appreciated

DB - Oracle 10G


RE: Count / group by

select distinct entity2
from st
where entity2 not in
(select entity2 from st
 group by entity2,"year"
 having count(*) > 1);

Note: YEAR is a reserved word in ANSI SQL, thats why its "quoted" above.

RE: Count / group by

I have now simplified my example:
SQL>select entity2 from st
SQL&group by entity2 having count(distinct entity1) = 1;

                  2 rows found


RE: Count / group by

Please, disregard my post as it is all wrong.

RE: Count / group by

Thanks to both of you for your assistance.

Much appreciated

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close