0 Replies Latest reply on May 13, 2016 1:14 PM by Ralphr

    Multiple Joins Cause Incorrect Report Calculations

    Ralphr

      I have a report that counts the number of physical CPU's in a server.  Its a simple report that does a join between the Nodes table and the AssetInventory_Processor table on NodeID then does a count on AssetInventory_Processor.NodeID.  Now I want to add Volume information to this report but as soon as I add another  join to Volumes.NodeID the CPU Count incorrectly jumps up.  I understand why this is happening but I don't know how to get around it. I tried using table and column aliases but that didn't work, although given that this was my  first try at using aliases it may  have been user error.  Below  is the simple SQL for the working report and I'm looking to add access to the Volumes table.  Thanks in advance

       

      SELECT

      Nodes.NodeID AS NodeID, Nodes.Caption AS Node_Name, COUNT(AssetInventory_Processor.NodeID) AS ProcID,  SUM(AssetInventory_Processor.NumberofCores) AS TotalCores

       

      FROM  Nodes  Inner Join AssetInventory_Processor  ON (Nodes.nodeid=AssetInventory_Processor.nodeid)

       

      WHERE

        ((Nodes.Function1 = 'Server' ) AND  (nodes.Function2 != 'Virtual Host') AND (Nodes.function3 != 'Guest'))

       

      Group By Nodes.NodeID, Nodes.Caption,  AssetInventory_Processor.NumberofCores, AssetInventory_Processor.NodeID

       

      ORDER BY 2 ASC