Counting distinct items within a category on SQL

Question

I need to compose a SQL statement as follows:

I have a table with many items, each item having a category. In total there are 3 categories.

I need to select the DISTINCT categories and then order them by number of items within each category.

Would this be a good way? Or too slow?

SELECT DISTINCT category, count(*) AS counter
FROM item_descr
GROUP BY category
ORDER BY counter DESC

Solution 1

The DISTINCT is not needed since you are using GROUP BY category:

SELECT category, count(*) AS counter
FROM item_descr
GROUP BY category
ORDER BY counter DESC

Solution 2

The GROUP BY is doing what you want. DISTINCT is redundant.

Comments

[Gordon Linoff]

This statement is, in fact, probably not true, because the category has too few values for the index to be effective.

[John McKnight]

You don't know that. What the user said was "I have a table with many items, each item having a category" The table has many items but only 3 category options. For all we know the table has 10 items or 10 million. You cannot guess table size based on the cardinality of the category field in the table. If you can use an index to effectively eliminate one third of a large database, it will help.

[Gordon Linoff]

The issue with the index is about the number of categories, not the number of items. Three categories means that the index is going to have very, very high selectivity. It probably won't have much impact on performance.

[John McKnight]

I agree it may not have much impact but in some situations any boost you can get is a positive one especially if you need to consider user perception of speed.