TSQL: Group by one column, count all rows and keep value on second column based on row_number

user_2309097 0
Viewed 339

I have a query that returns an Id, a Name and the Row_Number() based on some rules.

The query looks like that

SELECT 
    tm.id AS Id,
    pn.Name AS Name,
    ROW_NUMBER() OVER(PARTITION BY tm.id ORDER BY tm.CreatedDate ASC) AS Row
FROM 
    #tempTable AS tm
LEFT JOIN 
    names pn WITH (NOLOCK) ON tm.nameId = pn.NameId
WHERE ....

The output of the above query looks like the table below with the dummy data

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);

What I try to do, is group by the id field, count all rows, but for the name, use the one with row = 1

My attempt is like this, but, obviously, I get different rows since I include the x.name in the group by.

SELECT 
    x.id,
    x.name,
    COUNT(*) AS Value
FROM
    (SELECT 
         tm.id AS Id,
         pn.Name  AS Name,
         ROW_NUMBER() OVER(PARTITION BY tm.id ORDER BY tm.CreatedDate ASC) AS Row
     FROM 
         #tempTable AS tm
     LEFT JOIN 
         names pn WITH (NOLOCK) ON tm.nameId = pn.NameId
     WHERE ....
) x
GROUP BY 
    x.id, x.name
ORDER BY 
    COUNT(*) DESC

The desired results from the dummy data are:

id    name   count
------------------
 1    John     2
 2    Mary     1
 3    Jeff     1
 4    Bill     4
 5    Bobby    4
sql-server-2008 tsql sql-server
2 Answers

You can use FIRST_VALUE() window function to get the name of the row with row number = 1 and with the keyword DISTINCT there is no need to GROUP BY:

SELECT DISTINCT tm.id AS Id
     , FIRST_VALUE(pn.Name) OVER (PARTITION BY tm.id ORDER BY tm.CreatedDate ASC) AS Name
     , COUNT(*) OVER (PARTITION BY tm.id) AS counter
FROM #tempTable AS tm
LEFT JOIN names pn WITH (NOLOCK) ON tm.nameId = pn.NameId
WHERE ....

If you can't use FIRST_VALUE() then you can do it with conditional aggregation:

SELECT id,
       MAX(CASE WHEN Row = 1 THEN Name END) AS NAME,
       COUNT(*) AS Counter
FROM (
  SELECT tm.id AS Id
       , pn.Name  AS Name
       , ROW_NUMBER() OVER(PARTITION BY tm.id ORDER BY tm.CreatedDate ASC) AS Row
  FROM #tempTable AS tm
  LEFT JOIN names pn WITH (NOLOCK) ON tm.nameId = pn.NameId
  WHERE ....
) t
GROUP BY id
user_104988280

This could be one solution to your problem: group on both id and the target name (case when p.row = 1 then p.name end) for the counting. Adding a with rollup to the grouping will "roll up" the count aggregations. Another aggregation on just id can then be use to merge the row values from the intermediate data set (visible in fiddle).

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;

Fiddle

This would be another solution: do a regular count query with grouping on id and fetch the required name afterwards with a cross apply.

with cte as
(
  select p.id,
         count(1) as cnt
  from people p
  group by p.id
)
select cte.id,
       n.name,
       cte.cnt as [count]
from cte
cross apply ( select p.name
              from people p
              where p.id = cte.id
                and p.row = 1 ) n;

Fiddle

user_16079510
Related