How can I optimize my query?

I want to count the number of interface down and up and write this code that count them from two tables Nodes and Interface. This code works but I want to know any other way or optimize of this code? what is the best way to count them?

       SELECT
    q1.NodeName,
    q1.Nup as up, 
    q2.ndown as down
   FROM (
    SELECT
        Nodes.NodeID AS NodeID,
        Interfaces.NodeID AS InterfaceID,
        Nodes.Caption AS NodeName,
        Interfaces.Status as Status,
        Count(Nodes.Caption) as Nup 
        FROM 
        Nodes INNER JOIN Interfaces 开发者_如何学PythonON (Nodes.NodeID = Interfaces.NodeID)
        WHERE  
        (
          (
           (Interfaces.Status = '2'))
        )
        GROUP BY  Nodes.NodeID, Nodes.Caption, Interfaces.Status, Interfaces.NodeID
    ) AS q1
    INNER JOIN (
SELECT   
Interfaces.NodeID AS InterfaceID,
Nodes.Caption AS NodeName,
Interfaces.Status as Status,
Count(Nodes.Caption) as ndown
FROM 
Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
WHERE  
(
  (
   (Interfaces.Status = '1'))
)

GROUP BY  Nodes.NodeID, Nodes.Caption, Interfaces.Status, Interfaces.NodeID

   ) AS q2

 ON (q1.NodeID = q2.InterfaceID)
order by down Desc

You can combine the queries:

SELECT
    Nodes.Caption AS NodeName,
    Count(
      CASE WHEN Interfaces.Status = '2'
        THEN 1 
        ELSE NULL 
      END) as up,
    Count(
      CASE WHEN Interfaces.Status = '1'
        THEN 1 
        ELSE NULL 
      END) as down
FROM Nodes 
  INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
WHERE Interfaces.Status IN('1', '2')
GROUP BY  Nodes.NodeID, Nodes.Caption
order by 3 Desc

Note: in your original query, if a interface is always up, or always down, it would not show up in the resultset since you were using a INNER JOIN.

I would simplify the inner query on just the interfaces since that's the entire basis of the WHERE, no need to join to Nodes until THAT resultset is complete, THEN join to Nodes once the single node is done.

SELECT 
      Nodes.NodeName,
      Q1.CountUp as Up,
      Q1.CountDown as Down
   FROM
      ( SELECT
              Interfaces.NodeID AS InterfaceID,
              SUM( CASE WHEN Interfaces.Status = '1' then 1 else 0 end ) as CountUp,
              SUM( CASE WHEN Interfaces.Status = '2' then 1 else 0 end ) as CountDown
           FROM
              Interfaces 
           WHERE
              Interfaces.Status IN ('1','2')
           GROUP BY
              1 ) AS q1
      INNER JOIN Nodes
         ON q1.NodeID = Nodes.NodeID
   order by 
      3 desc