Clever Geek Handbook

SQL query to display count, if same name comes in adjacent row it should show counter else 1

I have a table tb1

with columns id,name

,

if the same name comes in an adjacent row it should display a count else 1

For example,

id                name

1                 sam

2                 jose

3                 sam

4                 sam

5                 dev

6                 jose

The result wants to be

name                 counts

 sam                   1

 jose                  1

 sam                   2

 dev                   1

 jose                  1

Please, help.

+3


source to share


3 answers


Try a combination with a sub-query: "COUNT (*) OVER (PARTITION" and row_number ():

--DROP TABLE #Test;
SELECT id = IDENTITY(INT,1,1), name INTO #Test FROM 
(
    SELECT name = 'sam' UNION ALL
    SELECT 'jose' UNION ALL
    SELECT 'sam ' UNION ALL
    SELECT 'sam ' UNION ALL
    SELECT 'sam ' UNION ALL
    SELECT 'dev ' UNION ALL
    SELECT 'dev ' UNION ALL
    SELECT 'jose' UNION ALL
    SELECT 'sam ' UNION ALL
    SELECT 'sam ' UNION ALL
    SELECT 'jose' 
) a;

GO

WITH GetEndID AS (
    SELECT *
    , EndID =(SELECT MIN(id) FROM #Test b WHERE b.name != a.name AND b.id > a.id)
    FROM #Test a
), GetCount AS
(
    SELECT 
    *
    , NameCount = COUNT(*) OVER (PARTITION BY EndID)
    , OrderPrio = ROW_NUMBER() OVER (PARTITION BY EndID ORDER BY id)
    FROM GetEndID
)
SELECT id, name, NameCount FROM GetCount WHERE OrderPrio = 1 ORDER BY id;


enter image description here

+1


source


Check this out: (SELF JOIN)

create table #sampele(id int,name varchar(50))

insert into #sampele values(1,'sam')
insert into #sampele values(2,'jose')
insert into #sampele values(3,'sam')
insert into #sampele values(4,'sam')
insert into #sampele values(5,'dev')
insert into #sampele values(6,'jose')


select a.id,a.name,case when a.name = b.name then 2 else 1 end as cnt from
#sampele a 
left outer join
#sampele b
on a.id = b.id+1


enter image description here

+3


source 


select distinct a.name,case when a.name = b.name then 2 else 1 end as cnt from
tb1 a 
left outer join
tb1 b
on a.id = b.id+1

sQlfiddle

Click to see how it works

+1


source






More articles:


All Articles