sql - Find the Row count of each value -

i have table like

name b b c c c a b b 

i need query return output like

name         count             1 b             2 c             3             2 b             2 

i tried rank(),dense_rank().but not able output

in opinion question not down vote it, it's interesting, anyway lets go on solution.

in order group names , counts of names in every separated group, simple count function group or window functions not solve problem, prefer use 2 helper fields 1 row number, other holds value group number, you'll iterate through table , increase value of group field next name if it's not same current:

assuming table is:

create table tbln (name varchar(10)) insert tbln values ('a'), ('b'), ('b'), ('c'), ('c'), ('c'), ('a'), ('a'), ('b'), ('b'); 

following query above explanation:

    ;with cte1 as(         select 1 gp,name -- add gp group number         tbln       ),     cte2 as(         select gp,name,                row_number() over(order gp) rn --add rn evaluating groups          cte1       ),     cte3 as(         select gp,name,rn cte2 rn=1         union         select case --evaluate groups                 when c2.name=c3.name c3.gp                  else c3.gp+1                end gp,                c2.name,c2.rn         cte3 c3         join cte2 c2 on c3.rn+1=c2.rn       ) select gp,name cte3 --[1] 

result:

gp  name 1   2   b 2   b 3   c 3   c 3   c 4   4   5   b 5   b 

now in above query instead of line [1] use below query:

select name , count from( select top 1000 gp,name,        count(name) count cte3 group gp,name order gp) q 

result:

name    count       1 b       2 c       3       2 b       2