Getting number of records against each row using SQL server

Question

I have table

col1 col2
---- ---- 
a    rrr
a    fff
b    ccc
b    zzz 
b    xxx

i want a query that return number of occurrences of col1 against each row like.

rows col1 col2
---- ---- ---- 
2    a    rrr
2    a    fff
3    b    ccc
3    b    zzz 
3    b    xxx

As a is repeated 2 time and b is repeated 3 time.

Answer 1

You can try Count over partition_by_clause which divides the result set produced by the FROM clause into partitions to which the function is applied. This function treats all rows of the query result set as a single group

Try this...

select count (col1) over (partition by col1) [rows] ,col1 ,col2 from tablename 

Answer 2

You can use the OVER clause with aggregate functions like COUNT:

SELECT rows = COUNT(*) OVER(PARTITION BY col1),
       col1,
       col2
FROM dbo.TableName

Demo