Need help in query for percent on nodes down currently

faizan_123

suppose if there are 100 nodes in the environment and 23 nodes are down then the query should give the output as 23%, i will be building on the base query but the base query is giving me some errors

i have made a draft SWQL query but the output is giving me some errors about aliases missing

Base Query

<span>select (select count(*) from Orion.Nodes where Status=2 )*100/(Select count(*) from Orion.Nodes) from Orion.Nodes</span>

any help would be appreciated

KMSigma.SWI

SELECT ROUND(((DownCount / TotalNodes) * 100), 2) AS [DownPercent]
FROM (
      SELECT SUM(CASE [Nodes].Status
                        WHEN 2
                              THEN 1.0
                        ELSE 0
                        END) AS [DownCount]
            ,COUNT([Nodes].Caption) AS [TotalNodes]
      FROM Orion.Nodes AS [Nodes]
      )

You can't have "unnamed" columns in SWQL - even in subqueries, so I elected to do it a slightly longer way, but one that might be easier to comprehend for new people.

Also when you have an integer (like the result of a COUNT) function and multiple or divide it by something, the result is returned in an integer.  I'm "tricking" the call by switching the returned result to a real number (1.0 vs. 1) and then summing that.