How to get number of duplicate Rows of DISTINCT co

Question

How can I make the following Column

------------
MakeDistinct
------------
CAT
CAT
CAT
DOG
DOG
PIN
PIN
PIN
PIN

As Shown Below

-------------   -------
AfterDistinct   Count
-------------   -------
CAT              3
DOG              2
PIN              4

Answer 1

SELECT MakeDistinct AS AfterDistinct, COUNT(*) AS [COUNT] FROM tablename 
GROUP BY MakeDistinct

Answer 2

Use COUNT() function by grouping MakeDistinct column using GROUP BY clause.

  SELECT MakeDistinct AS AfterDistinct
       , COUNT(MakeDistinct) AS Count
    FROM MyTable
GROUP BY MakeDistinct

Output:

╔═══════════════╦═══════╗
║ AFTERDISTINCT ║ COUNT ║
╠═══════════════╬═══════╣
║ CAT           ║     3 ║
║ DOG           ║     2 ║
║ PIN           ║     4 ║
╚═══════════════╩═══════╝

See this SQLFiddle

Answer 3

Please try:

select 
    MakeDistinct AfterDistinct, 
    COUNT(*) [Count] 
from 
    YourTable
group by MakeDistinct