SQL query input

Question

I have this query

Select 
    APM_Application.NAME AS AppName, APM_Application.ID AS AppID,
    APM_Application.NodeID, NodesData.Caption,
    APM_Component.ID AS CompID, APM_Component.NAME AS C
FROM 
    APM_Application 
INNER JOIN
    NodesData ON APM_Application.NodeID = NodesData.NodeID 
INNER JOIN 
    APM_Component ON APM_Application.ID = APM_Component.ApplicationID 
WHERE 
    NodesData.EngineID = 1

I want to count the number of NodeID instances. e.g 37 = Count is 9 and show it on the result

TIA

Answer 1

One option would be to join to a subquery which finds the counts:

SELECT a1.NAME AS AppName,
       a1.ID AS AppID,
       a1.NodeID,
       nd.Caption,
       ac.ID AS CompID,
       ac.NAME AS C,
       a2.node_count
FROM APM_Application a1
INNER JOIN
(
    SELECT NodeID, COUNT(*) AS node_count
    FROM APM_Application
    GROUP BY NodeID
) a2
    ON a1.NodeID = a2.NodeID
INNER JOIN NodesData nd
    ON a1.NodeID = nd.NodeID
INNER JOIN APM_Component ac
    ON a1.ID = ac.ApplicationID 
WHERE nd.EngineID = 1

This would be your best option if you were using MySQL. If, on the other hand, you are using a database like SQL Server or Oracle, then you could use COUNT() as a window function.