1 Reply Latest reply on Aug 31, 2018 1:32 PM by equalswql

    SQL Query

    msarkar

      Hi

      Could anyone please help to get optimized query for the following scenario:

       

      I have a table like this:

       

       

      Type

      Value

      A

      10

      B

      20

      C

      15

      D

      25

       

      I like to have output like this:

       

      Type

      Value

      A

      10

      B

      20

      Total

      30

      C

      15

      D

      25

      Total

      40

       

      /MSARKAR

        • Re: SQL Query
          equalswql

          Hello msarkar,

           

          I recommend using SWQL for a number of reasons, primarily as it is more protected from schema changes than SQL, hence greater sustainability for your query to survive in the wild so to speak. I do still use SQL when absolutely necessary, and using swql definitely has challenges in cases like this as it doesn't have the same exact features as SQL.

          In any case, here is a sample of how you could accomplish this using SWQL. You would only need to download SWQL studio on your local machine, connect using solarwinds credentials, and use the below info:

           

          1) Query to get full list of values from your table:

          SELECT DISTINCT MachineType

          FROM Orion.Nodes

           

          2) Select values and add to WHERE / IN statement (or use whatever logic you like, this was just demonstrating static values)

           

          3) This:

           

          SELECT  n.MachineType as [Type], COUNT(n.NodeID) as [Value]

          FROM Orion.Nodes n

          WHERE N.MachineType IN ('Windows 2008 Server','Windows 2008 R2 Server')

          GROUP BY n.MachineType

           

          UNION ALL(

          SELECT 'PostWindows2008 Total' as [Type],  COUNT(n.NodeID) as  [Value]

          FROM Orion.Nodes n

          WHERE N.MachineType IN ('Windows 2008 Server','Windows 2008 R2 Server')

          )

           

          UNION ALL(

          SELECT  n.MachineType as [Type], COUNT(n.NodeID) as [Value]

          FROM Orion.Nodes n

          WHERE N.MachineType IN ('Windows 2012 R2 Server','Windows 2016 Server')

          GROUP BY n.MachineType

          )

           

          UNION ALL(

          SELECT  'PostWindows2008 Total' as [Type] , COUNT(n.NodeID) as [Value]

          FROM Orion.Nodes n

          WHERE N.MachineType IN ('Windows 2012 R2 Server','Windows 2016 Server')

          )

           

          Thank you,

           

          Justin Valdez

          Systems Engineer, Tobias International

          justin.valdez@tobiassystems.com

          1 of 1 people found this helpful