I've been stuck on how to write a particular query for the following question:

How many employees are in how many businesses?

My end result should look like this:

So there are 23473423 businesses connected to 1 employee, 23473423 businesses connected to 2 employees, etc.

I have a table with a list of items including EmployeeId and BusinessId. A BusinessId can connect to many EmployeeIds. So far I have the following code to get me employees per business

Select BusinessId,
    Count(EmployeeId) as EIdCount
From Table
Group by BusinessId

Which gets me me a list of BusinessIds and how many EmployeeIds are attached to it.

But now I need to figure out how to further group it to where the BusinessId's can be grouped by the Grouped Counted Employee Ids. I've looked at subqueries, having by, and group but I am still at a loss how to progress this without running into an error. Thank you for your help in advance!

CodePudding user response：

Not sure if this is what you want:

Select EIdCount, COUNT(BusinessId)
FROM (
  Select BusinessId,
      Count(EmployeeId) as EIdCount
  From Table
  Group by BusinessId) A
Group by EIdCount

CodePudding user response：

Just use a subquery:

select
    EIdCount,count(BusinessId) as [BusinessId Count]
from
(
--your original query/start
    Select BusinessId,
        Count(EmployeeId) as EIdCount
    From Table
    Group by BusinessId
--your original query/end
)t
group by EIdCount