Combine two large multi-JOIN and GROUP BY queries

July 19, 2022

So I have one query that is performing one GROUP BY that requires a lot of joining to get it to perform aggregation of a row count basically

    SELECT ae.ServerName, Count(ccs.ApplicationID) [ComponentCount] 
    FROM [dbo].[APM_CurrentComponentStatus] ccs
    JOIN [APM_Applications] ap ON ccs.ApplicationID = ap.ID
    JOIN [Nodes] node ON ap.NodeID = node.NodeID
    JOIN [Engines] e ON node.EngineID = e.EngineID
    JOIN [AllEngines] ae ON ae.EngineID = e.EngineID
    --WHERE ap.Unmanaged = 'False'
    GROUP BY ae.ServerName
    ORDER BY [Component Count] DESC

Then there is a similarly structured query but performing a different aggregation on a row count.

    SELECT ae.servername, count(i.interfaceID) [InterfaceCount]
    FROM interfaces i 
    JOIN [Nodes] node ON i.NodeID = node.NodeID 
    JOIN [Engines] e ON node.EngineID = e.EngineID 
    JOIN [AllEngines] ae ON ae.EngineID = e.EngineID 
    group by ae.servername

How would you nest the second query into the first query so that the final columns would be something like
ServerName, ComponentCount, InterfaceCount ?

it seems like I’m missing something simple here but maybe not, maybe this is way beyond my capabilities.

>Solution :

I do not recommend this type of query, it is better to optimize it. Anyway, one of the methods of making the final result is as follows

Select 
  q1.ServerName, 
  q1.ComponentCount, 
  q2.InterfaceCount 
From 
  (
    SELECT 
      ae.ServerName, 
      Count(ccs.ApplicationID) [ComponentCount] 
    FROM 
      [dbo].[APM_CurrentComponentStatus] ccs 
      JOIN [APM_Applications] ap ON ccs.ApplicationID = ap.ID 
      JOIN [Nodes] node ON ap.NodeID = node.NodeID 
      JOIN [Engines] e ON node.EngineID = e.EngineID 
      JOIN [AllEngines] ae ON ae.EngineID = e.EngineID --WHERE ap.Unmanaged = 'False'
    GROUP BY 
      ae.ServerName 
    ORDER BY 
      [Component Count] DESC
  ) q1 
  Inner Join (
    SELECT 
      ae.servername, 
      count(i.interfaceID) [InterfaceCount] 
    FROM 
      interfaces i 
      JOIN [Nodes] node ON i.NodeID = node.NodeID 
      JOIN [Engines] e ON node.EngineID = e.EngineID 
      JOIN [AllEngines] ae ON ae.EngineID = e.EngineID 
    group by 
      ae.servername
  ) q2 on q1.ServerName = q2ServerName