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
shyam
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;
+1
Jarle bjørnbeth
source
to share
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
+3
knkarthick24
source
to share
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
Ali786
source
to share