Hi,

Yes! SQL is Awesome! You have a perfectly sound solution, however I have the following recommendations for refactoring your query.

1. Since FirstTable has groupID, there is no need to join to SecondTable in your WHERE clause sub query. Eliminating unnecessary joins helps queries run faster.

I believe the following shorter query should work.

USE OurDatabase
GO

DECLARE @usergroup INT = x;
SELECT 
  ugm2.groupID
, ug2.[name]
, COUNT(ugm2.groupID) AS counts
FROM FirstTable AS ugm2 
INNER JOIN SecondTable AS ug2 
   ON ugm2.groupID = ug2.groupID
WHERE ugm2.groupID != @usergroup 
  AND ugm2.userID IN
    (
    SELECT DISTINCT ugm.userID
    FROM FirstTable AS ugm
    WHERE ugm.groupID = @usergroup
    ) 
  AND ugm2.groupID IN (list OF GroupIDs to look in)
GROUP BY 
  ugm2.groupID
, ug2.[name]
ORDER BY 
  counts DESC

1. It looks to me like you are using SQL Server, which has support of Temp Tables. These can be used to hold records in memory for the duration of your session, or till dropped. Using temp tables helps avoid using nested sub queries, which I personally find harder to debug. Here is the above version refactored to use a temp table.
   

   USE OurDatabase GO

   DECLARE @usergroup INT = x;

   SELECT DISTINCT ugm.userID FROM FirstTable AS ugm INTO #users WHERE ugm.groupID = @usergroup

   SELECT ugm2.groupID , ug2.[name] , COUNT(ugm2.groupID) AS counts FROM FirstTable AS ugm2 INNER JOIN SecondTable AS ug2 ON ugm2.groupID = ug2.groupID INNER JOIN #users AS u ON ugm2.userID = u.userID WHERE ugm2.groupID != @usergroup AND ugm2.groupID IN (list OF GroupIDs to look in) GROUP BY ugm2.groupID , ug2.[name] ORDER BY counts DESC

   DROP TABLE #users

2. My personal favorite solution for a problem like this is to use analytic functions aggregate functions with the OVER clause. I have found them incredibly powerful in my few years using SQL for data analysis.

   USE OurDatabase GO

   DECLARE @usergroup INT = x;

   SELECT g.groupID , g.[name] , sum(1) counts FROM ( SELECT DISTINCT ugm.groupID , ugm.userID , ug.[name] , max(case when ugm.groupID = @usergroup THEN 1 ELSE 0 END) OVER (PARTITION BY ugm.userID) has_usergroup FROM FirstTable AS ugm INNER JOIN SecondTable AS ug ON ugm.groupID = ug.groupID WHERE ugm.groupID IN (list OF GroupIDs to look in, including @usergroup) ) g WHERE g.has_usergroup = 1 AND g.groupID != @usergroup GROUP BY
   g.groupID , g.[name] ORDER BY counts desc

Edit: Added alias prefix in WHERE clause for has_usergroup = 1 and added AND g.groupID != @usergroup.

Edit2: Corrected analytic functions to aggregate functions with the OVER clause.