January 20, 2022 at 3:46 pm
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
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
Thanks, exactly what I needed
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy