Further Group by Count

Question

Current SQL Statement:

SELECT count(installUserID) as userCount
  FROM installUsers
  group by installationID

Current output:

userCount
4
2
1
1
1
2
4
1
3
1

I am trying to count the number of times the userCount is 1, as well as 2, etc...

Example of desired output:

userCount = 1, 5 times
userCount = 2, 2 times
userCount = 3, 1 times
userCount = 4, 2 times

I've looked around the internet and I am thinking there is something I can do with aggregate functions, perhaps with grouping, but I am still unclear of how to go about implementing this with pure sql.

Answer 1

It appears you want a count of the counts. To do this you can use an outer select:

SELECT userCount, COUNT(*) AS userCountcount
FROM
(
    SELECT COUNT(installUserID) as userCount
    FROM installUsers
    GROUP BY installationID
) AS T1
GROUP BY userCount
ORDER BY userCount

Answer 2

Alternatively you can use a CTE. Maybe over kill for this but comes in mighty handy.

WITH count_cte(usercount)
AS
(
  SELECT COUNT(installUserID) AS userCount
  FROM installUsers
  GROUP BY installationID
)

SELECT  usercount  ,COUNT(usercount) AS times  FROM count_cte GROUP BY usercount