This query fails when I add the line shown...

:BEGIN FAIL:
I have this so far
Select Companyid, count(*) as cnt
from mytable
where State is not null
and cnt = 1  <------------------------- FAIL
group by CompanyID
:END FAIL:

Any way to do this?

Here's a long winded background if it'll help....

I have a single table query. here's a sample of the table:

CompanyID, State
1,OH
1,IL
1,NY
2,IL
3,NY
3,OH
4,NY
5,CA
5,WA

I want a query that'll return something like this:

2,IL
4,NY

I have this so far Select Companyid, count(*) as cnt from mytable where State is not null group by CompanyID

This gives me a count of the number of records for each company. IE:

1,3
2,1
3,2
4,1
5,2

Now I want to filter the above list to just the two records with one result.

I tried adding another where clause, but it failed:

BEGIN FAIL:
I have this so far
Select Companyid, count(*) as cnt
from mytable
where State is not null
and cnt = 1  <-------------------- FAIL
group by CompanyID
END FAIL: