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!!

Andrew Moore · Accepted Answer · 2011-03-02 16:08:27Z

9

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

answered Mar 2, 2011 at 16:08

Andrew Moore

95.8k30 gold badges167 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1