Make a count of a SQL count

Question

Make a count of a SQL count

Navigation

#1 by (2 votes)

1

Hello, I have the following query

Select count(device) FROM entrada
where badge = badge
group by badge
order by count desc;

and I get a list of the times each badge has a device, what I want now is to join for example if the badge one is 5 and the badge 2 is 5 also give me a result like that

Cantidad Contados

    5        2

A more explained example would be the query returns me the following

'8'
'7'
'7'
'7'
'7'
'7'
'7'
'7'
'7'
'6'
'6'
'6'
'6'
'6'
'6'

Then it would be to group them

Cantidad Contados
    8       1
    7       8
    6       6

I want you to group the results by counting them, I can not do this query

sql postgresql

asked by R. Nuñez 28.05.2018 в 18:51

source

1 answer

Date format in rails Doubt between try-catch and throws

score 2 · Accepted Answer

Ok, what you need is to use your current query as a derived table:

SELECT  Cantidad, 
        COUNT(*) Contados
FROM (  SELECT badge, COUNT(device) Cantidad
        FROM entrada
        GROUP BY badge) as T
GROUP BY Cantidad
;

Or a CTE:

WITH CTE AS
(
    SELECT badge, COUNT(device) Cantidad
    FROM entrada
    GROUP BY badge
)
SELECT  Cantidad, 
        COUNT(*) Contados
FROM CTE
GROUP BY Cantidad
;