CODEWITHSUNDEEP
sql-server
A Houghton
A Houghton

Reputation: 392

SQL count, group by, and retrieve related data from another table

I've written the following query:

SELECT [User], count(*) as Records 
FROM dbo.PBBuilds 
GROUP BY [User] 
ORDER BY [Records] DESC

It selects a USERID and counts the amount or rows associated as 'records'.

--------------------------------------------------
| Users                                 | records | 
------------------------------------------------- |
| 0ca3f535-96f2-4926-9b4d-25fb2aa713eb  |    3    |
| a32d1784-d636-4caf-bb9d-60bfbfc1aadf  |    2    |
| 5fabb0aa-f61f-4180-90f6-5dd1650fd0db  |    1    |
---------------------------------------------------

The Users column relates to an ID column in another table where the users name is stored. How can I retrieve and match the data by this link?

Upvotes: 1

Views: 35

Answers (1)

Matt
Matt

Reputation: 13389

Assuming your other table is called other_table and also has the ID in column User, then:

SELECT UserName, COUNT(*) Records 
FROM dbo.PBBuilds b INNER JOIN other_table o
on b.User = o.User
GROUP BY UserName
ORDER BY COUNT(*) DESC

Upvotes: 2

Related Questions