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: EmployeeId Count BusinessId Count 1 23473423 2 56245764 3 834456 So there are 23473423 businesses that have 1 employee, 23473423 businesses that have 2 employees, etc. I have a table with

SQL How many of a counted row is in another counted row?

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:

EmployeeId Count	BusinessId Count
1	23473423
2	56245764
3	834456

So there are 23473423 businesses that have 1 employee, 23473423 businesses that have 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.

BusinessId	EIdCount
23	2
24	5
25	1
26	3

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!

Answer

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

Advertisement

Answer