SQL GROUP BY and COUNT with multiple columns

I know I’m just making a simple error somewhere, but I can’t figure it out. I’m sure this is beyond easy.

I have a table with user ids, names, and a profile. I’m trying to count the number of users with each profile, and group them by the profile. Below is a sample of fake data.

This is the kind of output I’m looking for:

Below are a few simplified versions of what I tried so far:

select UserID, Name, Profile 
from test_db
group by profile

Another attempt:

select UserID, Name, Profile, count(Profile)
from test_db
group by profile, UserID, Name

Neither give me what I’m looking for. I just get a long list of rows and for the count they just all have a 1 by them. I know I’m making it harder than it needs to be.

Thanks in advance!

Answer

Use ROW_NUMBER() and COUNT() window functions:

SELECT CASE WHEN t.rn = 1 THEN t.Profile END Profile,
       CASE WHEN t.rn = 1 THEN t.counter END counter,
       t.User_ID,
       t.Name
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY Profile ORDER BY Name) rn,
         COUNT(*) OVER (PARTITION BY Profile) counter
  FROM tablename t
) t
ORDER BY t.Profile, t.rn

I used the column Name to sort the rows in each Profile, but you can use any other column by making the change inside the OVER clause of ROW_NUMBER().