CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Leon Cullens
Leon Cullens

Reputation: 12496

How to get the occurrence frequency of SQL result?

I have a denormalized table in SQL Server 2012 containing publicly available data from the Windows Store. Each entry is an app, and my table (Apps) looks like this (simplified):

Id
StoreId
Name
Publisher
MainCategory
SubCategory
Rating
AmountofRatings

What I want to query now is a list of the occurrence frequency of the amount of apps per publisher. Hard te describe, so here's an example:

I want to know how many publishers have 10 apps in the store, how many publishers have 20 apps in the store, etc. etc.

I tried all different sorts of queries (including subqueries in the GROUP BY), but I just can't figure out how to get this data. Can anyone help me?

Upvotes: 1

Views: 2413

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

This is a frequency of frequency query:

select cnt, count(*), min(publisher), max(publisher)
from (select publisher, count(*) as cnt
      from t
      group by publisher
     ) t
group by cnt
order by 1

Upvotes: 1

Related Questions