How can I get the number of groups with only 1 member?

I'm trying

select count(*)
  from groups
  left join members on groups.id=members.group_id
 group by groups.id
having count(members.id)=1

but it just gives me a b开发者_开发百科unch of 1s. how can I get the row count?

Just count the results of your query by embedding it as a sub-query.

As a side note, an inner join would be more appropriate than an outer join for your query. In fact, you only need the join if there are invalid group_id values in the members table.

With inner join:

select count(*)
from
(
  select groups.id
  from groups 
  inner join members on groups.id=members.group_id
  group by groups.id
  having count(members.id)=1
) as sub

Without join (assuming no orphans):

select count(*)
from
(
  select group_id
  from members
  group_id
  having count(*) = 1
) as sub

Your asking for the count of each group having 1 member. So a bunch of ones is correct. You might try using a subquery like...

SELECT COUNT(*) FROM (
  select count(*)
    from groups
    left join members on groups.id=members.group_id
   group by groups.id
  having count(members.id)=1
)