ACsteel ACsteel - 5 months ago 13
SQL Question

TSQL with Group By Count and Rank

Let's say I have the following:

DECLARE @Name TABLE (Name VARCHAR(MAX));

INSERT INTO @Name VALUES ('bob'),('bob'),('john'),('john'),('mark'),('mark'),('mark'),('lisa');

SELECT Name,
COUNT(Name) AS TOTALS
FROM @Name
GROUP BY Name
ORDER BY TOTALS DESC;


How can I also use Rank() or Dense_Rank() to get the rankings based on the counts?

Answer

If you want a rank column, you can use it with group by:

SELECT n.Name, COUNT(*) as TOTALS,
       RANK() OVER (ORDER BY COUNT(*)) as Total_Rank
FROM @Name n
GROUP BY n.Name
ORDER BY TOTALS DESC;
Comments