Help needed with simple mysql group by query
- by Jack
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:
