Reputation: 12856

Using Count to find the number of occurrences

Let's say I have a table with the following values.

Ford
Ford
Ford
Honda
Chevy
Honda
Honda
Chevy

So I want to construct the following output.

Ford   3
Honda  3
Chevy  2

It just takes the count of each element in the column.

I'm having an issue listing the unique columns.

Can anyone tell me how to do this?

I've messed around with UNIQUE and DISTINCT, but I'm not able to get the list of values on the left.

Upvotes: 39

Answers (4)

Reputation: 43434

Do you mean this?

select car_made, count(*) from cars
group by car_made

Upvotes: 63

Reputation: 4030

Hope this works for you!!

SELECT car_brand, COUNT(id) from cars
GROUP BY car_brand

COUNT(id) with InnoDB is faster than COUNT(*) because InnoDB doesn't cache the row count.

Upvotes: 0

Reputation: 47311

select car_made, count(*) as occurrences
from cars
group by car_made
order by occurrences desc, car_made

Upvotes: 5

Reputation: 28316

SELECT ... GROUP BY

For example:
SELECT CarName, COUNT(CarName) AS CarCount FROM tbl GROUP BY CarName

Upvotes: 2