SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE people(
  id int,
  name varchar(55),
  row int
);

INSERT INTO people VALUES
(1, 'John', 1),
(1, 'John', 2),
(2, 'Mary', 1),
(3, 'Jeff', 1),
(4, 'Bill', 1),
(4, 'Bill', 2),
(4, 'Bill', 3),
(4, 'Billy',4),
(5, 'Bobby',1),
(5, 'Bob' , 2),
(5, 'Bob' , 3),
(5, 'Bob' , 4);

select * from people;


id
name
row


1
John
1

1
John
2

2
Mary
1

3
Jeff
1

4
Bill
1

4
Bill
2

4
Bill
3

4
Billy
4

5
Bobby
1

5
Bob
2

5
Bob
3

5
Bob
4


select p.id,
       case when p.row = 1 then p.name end as name,
       count(1) as cnt
from people p
group by p.id,
         case when p.row = 1 then p.name end with rollup;


id
name
cnt


1
null
1

1
John
1

1
null
2

2
Mary
1

2
null
1

3
Jeff
1

3
null
1

4
null
3

4
Bill
1

4
null
4

5
null
3

5
Bobby
1

5
null
4

null
null
12


with cte as
(
  select p.id,
         case when p.row = 1 then p.name end as name,
         count(1) as cnt
  from people p
  group by p.id, case when p.row = 1 then p.name end with rollup
  having grouping(p.id) = 0
)
select cte.id,
       max(cte.name) as name,
       max(cte.cnt) as [count]
from cte
group by cte.id;


id
name
count


1
John
2

2
Mary
1

3
Jeff
1

4
Bill
4

5
Bobby
4


Warning: Null value is eliminated by an aggregate or other SET operation.

id	name	cnt
1	null	1
1	John	1
1	null	2
2	Mary	1
2	null	1
3	Jeff	1
3	null	1
4	null	3
4	Bill	1
4	null	4
5	null	3
5	Bobby	1
5	null	4
null	null	12