MySQL get number of rows starting with same pattern without knowing it

Question

I have a MySQL database with a table containing MAC addresses and I want to know how many groups with the same manufacturer (3 first octets of the MAC) are.

I can select groups of macs with the same three starting octets:

select `mac` from `t1` where `mac` LIKE 'FC:E9:98%';

But that way I have to do it manually for each manufacturer. Is there any way to ask for every different group of macs starting with the same three octets without having to specify them?

Thanks!

EDITED:

Thanks guys for answering that fast. Both of these solutions worked fine:

select left(mac,8) mac , count(1) cnt from t1 group by left(mac, 8);

select count(*),mac from t1 group by SUBSTRING_INDEX(mac,':',3);

I still have too low reputation for voting up your answers but you saved me a lot of time +1! :)

Answer 1

you can get the number of rows with that mac prefix with a substring:

select count(*), substring(mac, 1, 8)
from t1
group by substring(mac, 1, 8)

Answer 2

This should work (though the LEFT function may only work for qsl server; I don't know, otherwise use substring method. Edit: looked it up: LEFT is also available in mysql). LEFT takes a string and an int as paramaters, returning the first (left) characters of the string, the number of which is determined by the second int parameter.

SELECT     LEFT(mac, 8) mac
,          COUNT(1) CNT
FROM       T1
GROUP BY   LEFT(mac, 8)

Answer 3

You can use the answer below :

SELECT mac FROM t1 GROUP BY substring(mac, 1, 8);

Answer 4

select mac from t1 Group by SUBSTRING_INDEX (mac,':',3);