Select distinct values, and count number of occurrences

Question

I have a list of classes and categories for the classes. I would like to display how many classes each category has. Something like this:

category | # of classes
Sports   | 12
Fitness  | 32
Climbing | 8

The table is set up so that each class is on a line that includes the category.

category | class_name
fitness  | Learn to Skate

I have the following SQL, which generates a list of distinct categories:

select 
    distinct category

from classes

order by category asc

; Output:
; Fitness
; Climbing
; Recreation
; ...

I also want to display how many classes there are. I figured this would be straightforward:

select 
    distinct category,
    count(distinct category)

from classes

order by category asc

; Output:
; Fitness | 11

The output almost works, but it only produces one row. Without the count() it produces 11 rows, as expected. Why does this only produce one row? How can I achieve this?

possible duplicate of [Do aggregate MySQL functions always return a single row?](http://stackoverflow.com/questions/1192019/do-aggregate-mysql-functions-always-return-a-single-row) — M Khalid Junaid, Aug 21 '14 at 19:34
Without knowing the term "aggregate function", it was impossible to find that question. I looked searched plenty of other questions to no avail :/ — Radley Sustaire, Aug 21 '14 at 19:39
Just google [**`Mysql count function`**](https://www.google.com.pk/search?q=mysql+count+function&oq=mysql+count+function&aqs=chrome..69i57.295j0j7&sourceid=chrome&es_sm=93&ie=UTF-8) second link will say it self for aggregate fucntion — M Khalid Junaid, Aug 21 '14 at 19:44
It's still a roundabout way to find the answer to my original question, and there is still no reason why I would have searched for what an aggregate function is. That's quite a tangent from what I was looking for. It's probably a good idea to have this question, even as a duplicate, if not just so that it shows up on in searches for others who don't know what an aggregate function is (or know that it is the key to the issue). — Radley Sustaire, Aug 21 '14 at 20:23
Possible duplicate of [MySQL: Count occurrences of distinct values](https://stackoverflow.com/questions/1346345/mysql-count-occurrences-of-distinct-values) — Oleg V. Volkov, Jan 31 '18 at 20:40

Ende Neu · Accepted Answer · 2014-08-21T20:04:08.737

6

You are using an aggregation function (COUNT) without a GROUP BY clause, then mysql doesn't know how you want to aggregate your data:

SELECT 
  category,
  count(category) as category_count
FROM classes
GROUP BY category
ORDER BY category ASC

edited Aug 21 '14 at 20:04

answered Aug 21 '14 at 19:31

Ende Neu

15,581
5
57
68

This almost worked, but the count is 1 for all 11 rows (I added `limit 2000`, but no efect) – Radley Sustaire Aug 21 '14 at 19:33
My bad, you should drop the `distinct` inside the `count`, saying `distinct category` is like counting them only once. – Ende Neu Aug 21 '14 at 19:34
Oh okay, I think I saw your edit but didn't notice it changed. That worked! (can't accept for 9 minutes though) – Radley Sustaire Aug 21 '14 at 19:35

Select distinct values, and count number of occurrences

1 Answers1