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

You can't use aggregate function results in a 'where' clause. WHERE is applied at the time th erow is retrieved. But that at point, the results of "count" aren't available yet. That's where 'HAVING' comes into play, which is like 'where', but is applied as the final stage of the query, just before the results are returned. — Marc B
– Marc B, Commented Apr 27, 2010 at 15:17

Peter Lang · Accepted Answer · 2010-04-27 13:21:36Z

3

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

answered Apr 27, 2010 at 13:21

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dnagirl · Accepted Answer · 2010-04-27 13:23:15Z

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;

answered Apr 27, 2010 at 13:23

dnagirl

20.5k13 gold badges86 silver badges127 bronze badges