Using the count aggregate in SQL (Informix)

I need to grab the id's out of a table where the count(id) is equal to 1.

In other words, I need to know the id's that only have one entry in this table...

I know this is simple but help me out...

select * from multi_race_rec where id in (select id  from multi_race_rec where count(id) = 1)

table schema:
race_no <--unique identifier
id <--- foreign key
race <--- two digit race code


I get a bad use of aggregate message when I try my idea on how to select them...
dcrowley_01Asked:
Who is Participating?
 
mustaccioCommented:
try "select id from multi_race_rec group by id having count(id) = 1". I didn't test it with Informix but this is standard SQL so I'm assuming it should work.
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Even simpler would be this:

select id,  
       count(*)         
  from multi_race_rec            
 group by id
having count(*) = 1

Open in new window


HTH,
DaveSlash
0
 
dcrowley_01Author Commented:
Thanks guys...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.