How to count the distinct column in mysql?

Question

E.g:

with the select query I got below result:

columnA columnB
type1   typea
type2   typea
type3   typeb
type4   typec
type5   typed
type6   typed
type7   typed

with the DISTINCT I only got the distinct result,but I also want to get the total number of each distinct.

and now I want to get the total number of

typea,typeb,typec and typed.

Just like:

columnB total
typea   2
typeb   1
typec   1
typed   3

Thank you very much!!

Answer 1

You can use GROUP BY to get results by type:

SELECT columnB, COUNT(*) AS 'total'
  FROM myTable
  GROUP BY columnB;

This should give you exactly the result you are looking for.

MySQL Documentation: 11.16.1 GROUP BY (Aggregate) Functions