Selecting data from one table and multiple counts from another

• MattNorman88

  SSC Veteran

  Points: 211

  More actions

  January 20, 2022 at 3:46 pm

  #3975291

  I have a table called AgentGroups which just has two columns, GroupID and GroupName.

  I then have two other tables AgentGroupMapping and SkillGroupMapping that both also have a GroupID column.

  I want to select all of the rows from the AgentGroups table and then the count from both of the other tables where the GroupID matches.

  I have tried the following, however the two counts come back with the same value:

  SELECT a.[GroupID], a.[GroupName], COUNT(b.[GroupID]) AS AgentCount, COUNT(c.[GroupID]) AS SkillCount

  FROM [ApexCCM].[dbo].[AgentGroups] a

  LEFT JOIN [ApexCCM].[dbo].[AgentGroupMapping] b ON a.[GroupID] = b.[GroupID]

  LEFT JOIN [ApexCCM].[dbo].[SkillGroupMapping] c ON a.[GroupID] = c.[GroupID]

  GROUP BY a.[GroupID], a.[GroupName]

• Michael L John

  One Orange Chip

  Points: 27079

  More actions

  January 20, 2022 at 4:28 pm

  #3975303

  How about:

  SELECT 
  a.[GroupID], 
  a.[GroupName], 
  b.AgentCount,
  c.SkillCount
  
  FROM [ApexCCM].[dbo].[AgentGroups] a
  
  LEFT JOIN (SELECT GroupID, COUNT(*) AgentCount
  FROM [ApexCCM].[dbo].[AgentGroupMapping] 
  GROUP BY GroupID) b ON a.[GroupID] = b.[GroupID]
  
  LEFT JOIN (SELECT GroupID, Count(*) SkillCount
  FROM [ApexCCM].[dbo].[SkillGroupMapping] 
  GROUP BY GroupID) c ON a.[GroupID] = c.[GroupID]
  

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/

• MattNorman88

  SSC Veteran

  Points: 211

  More actions

  January 20, 2022 at 8:15 pm

  #3975368

  Thanks, exactly what I needed