CODEWITHSUNDEEP
sql
subzeroLV
subzeroLV

Reputation: 73

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.

enter image description here

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

enter image description here

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!

Upvotes: 0

Views: 4561

Answers (2)

forpas
forpas

Reputation: 164054

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().

Upvotes: 1

JNevill
JNevill

Reputation: 50019

Chances are whatever RDBMS you are using supports window functions which you'll need for getting counts in an unaggregated result set like this.

 SELECT UserID
     ,Name
     ,Profile
     ,Count(*) OVER (PARTITION BY Profile)
 FROM test_db
 ORDER BY Profile;

That won't give you blanks for repeating Profile and Count columns, but that's not really a thing that RDBMS's do so this is probably as close as you get.

You'll notice there is no GROUP BY here. You don't want to GROUP BY anything since you want the same number of records outputted as you put in. That Count(*) OVER (PARTITION BY Profile) will group records by distinct Profile value and then give you a count for each group/partition.

Upvotes: 3

Related Questions