Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1344
  • Last Modified:

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...
0
dcrowley_01
Asked:
dcrowley_01
2 Solutions
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now