CODEWITHSUNDEEP
sqlsql-server
SpoonerNZ
SpoonerNZ

Reputation: 1935

Nested groups and counts within SQL

I can't eloquently explain my end problem, feel free to edit both this title and content of this question if you can describe the SQL solution better.

I have data in a table containing the columns "Item", "Category", and I have the following query calculating the number of distinct categories within each item.

SELECT 
  [ItemID], COUNT(DISTINCT [CategoryText] ) AS 'number of categories' 
FROM [SampleTable]
GROUP BY
  ItemID

This gives me the output

`ItemID', 'number of categories'
100 1
101 3
102 1

What I now want to do is group the number of items by the number of categories they have, with my aim being to determine 'Do most items only have one category?'

For the example above, I would expect the outcome to be 

'Number of categories', 'Number of items'
1, 2
3, 1

I'm sure there is a simple query to get to this but am going around in circles without making progress.

Upvotes: 2

Views: 46

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Aggregate the results by number of categories.

select [number of categories], count(*) [number of items]
from (SELECT [ItemID], COUNT(DISTINCT [CategoryText]) AS 'number of categories' 
      FROM [SampleTable]
      GROUP BY ItemID) t
group by [number of categories]

Upvotes: 3

Related Questions