CODEWITHSUNDEEP
sqlsql-server-2008
Subin Jacob
Subin Jacob

Reputation: 4864

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

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

Upvotes: 1

Views: 171

Answers (3)

Lakshmana Kumar
Lakshmana Kumar

Reputation: 1249

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

Upvotes: 0

TechDo
TechDo

Reputation: 18659

Please try:

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

Upvotes: 0

Himanshu
Himanshu

Reputation: 32612

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

Upvotes: 3

Related Questions