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

Result of the query

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

TIA

Please read meta.stackoverflow.com/questions/285551/… and the accepted answer — user330315
– user330315, Commented Mar 24, 2017 at 7:10

Tim Biegeleisen · Accepted Answer · 2017-03-24 02:27:48Z

2

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.

edited Mar 24, 2017 at 2:27

answered Mar 24, 2017 at 1:54

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query input

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related