Post reply

Selecting data from one table and multiple counts from another

  • 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]

  • 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/

  • January 20, 2022 at 8:15 pm

    #3975368

    Thanks, exactly what I needed

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply