group by count sql-CodePudding

I have a script

SELECT  customer_id, COUNT(*)
FROM devices 
GROUP BY customer_id
ORDER BY 2 desc;

and the result is

customer_id      count
1234              8
4567              7
8910              7
1112              7
1314              5
1516              5

but what I expect is

devices      customer
8              1
7              3
5              2

Is there any way without using a new table?

CodePudding user response：

Use your current query as a source for another:

with temp as
  -- this is your current query
  (select customer_id, 
          count(*) cnt
   from devices
   group by customer_id
  )
select cnt as devices, 
       count(*) as customer
from temp
group by cnt;

CodePudding user response：

The usual use of count(*) is to find out many rows have been aggregated by group function. Want you ant to do is aggregate your result in a second select, and ask for count(*) a second time to get result you've described.