sql - How to get number of duplicate Rows of DISTINCT column as another column? -

how can make following column

------------ makedistinct ------------ cat cat cat dog dog pin pin pin pin 

as shown below

-------------   ------- afterdistinct   count -------------   ------- cat              3 dog              2 pin              4 

use count() function grouping makedistinct column using group by clause.

  select makedistinct afterdistinct        , count(makedistinct) count     mytable group makedistinct 

output:

╔═══════════════╦═══════╗ ║ afterdistinct ║ count ║ ╠═══════════════╬═══════╣ ║ cat           ║     3 ║ ║ dog           ║     2 ║ ║ pin           ║     4 ║ ╚═══════════════╩═══════╝ 

see sqlfiddle