Help needed with simple mysql group by query

Question

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

Select Companyid, count(*) as cnt
from mytable
where State is not null
and cnt = 1  <------------------------- FAIL
group by CompanyID

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:

Select Companyid, count(*) as cnt
from mytable
where State is not null
and cnt = 1  <-------------------- FAIL
group by CompanyID

Answer 1

cnt is an aggregate value. Therefore it goes in a HAVING clause, not a WHERE clause.

Select Companyid, count(*) as cnt 
from mytable 
where State is not null 
group by CompanyID 
HAVING count(*)=1;

Answer 2

You can use a Having-clause to restrict to records which occur only once in your Group By:

Select companyId, state
From mytable
Where state Is Not Null
Group By companyId
Having Count(*) = 1