Using Count to find the number of occurrences

Question

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.

I'll give you a hint: group by is what will group like rows by a column. select count(*) from table group by car_type should get you there. — anq, Oct 04 '11 at 23:00

score 63 · Accepted Answer · edited Aug 05 '20 at 16:07

63

Do you mean this?

select car_make, count(*) from cars
group by car_makes

edited Aug 05 '20 at 16:07

answered Oct 04 '11 at 23:00

Mosty Mostacho

score 5 · Answer 2 · answered Oct 04 '11 at 23:24

5

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

answered Oct 04 '11 at 23:24

ajreal

score 2 · Answer 3 · answered Oct 04 '11 at 23:02

2

SELECT ... GROUP BY

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

answered Oct 04 '11 at 23:02

Polynomial

score 0 · Answer 4 · answered Aug 05 '20 at 16:21

0

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.

answered Aug 05 '20 at 16:21

Ankit Jindal

4 Answers4