Count values in column on join then group by Forgive me if this is super easy. I am doing some data analysis on the Stack Overflow database model. I need to count the number of badges a user has. They can have multiple of the same badge name.

For example:

A user can have 3 x Teacher badges, and 2 x Editor badges, etc.

I would like to be able to see it in the below format:

 UserName | Badge_Name | Amount
 ------------------------------
 JamesBond| Teacher    | 3
 JamesBond| Editor     | 2
 Arthur1  | Teacher    | 5


So far all I am able to retrieve is the display name of the user and the amount of badges they hold:

SELECT t2.DisplayName, COUNT(t1.Id) AS TotalNumberOfBadges
FROM Badges t1 
JOIN Users t2 ON t2.Id = t1.UserId 
GROUP BY t2.DisplayName 
ORDER BY t2.Id;
Without seeing the schema I've had to guess but it seems that you just need to add the Badge_Name to the SELECT list and the GROUP BY, like this:

SELECT t2.displayname AS UserName, t1.Badge_Name,
       Count(t1.id) AS TotalNumberOfBadges 
FROM   badges t1 
       join users t2 
         ON t2.id = t1.userid 
GROUP  BY t2.displayname, t1.Badge_Name
ORDER  BY t2.id; 


If your badges table contains a different column name for the name of the badge you will have to sub that in for where I've used Badge_Name.