2 Replies Latest reply on Oct 11, 2019 8:01 AM by tmcurry

    multiple count statements in SWQL and columns

    tmcurry

      In SWQL Studio, both queries work fine. However when I try to make these to queries one table it fails

      I breviated column names in example below.

      I tried a lot of varaiations that is why I just broke it down to its simplest form instead of showing all the things that did not work.

       

      1.

       

      SELECT DISTINCT

      RUN,

      Count(*) AS PD

      FROM Orion.NodesCustomProperties

           where NOPD > '0'

      Group by RUN

      order by RUN

       

      2.

      SELECT DISTINCT

      RUN,

      Count(*) AS SBUPD

      FROM Orion.NodesCustomProperties

          WHERE NOPD > '0'

          AND SB like 'SBU%'

      Group by RUN

      order by RUN

       

      Thank you

        • Re: multiple count statements in SWQL and columns
          mesverrum

          Since I don't have data like this in my environment I'm not entirely sure what the expected result is, but this seems like it would be correct to me, assuming that the run's are the same in both data sets, might have to play with the joins if thats not true.

           

           

          SELECT DISTINCT

          ncp.RUN,

          Count(*) AS PD

          , sbu.SBUPD

           

          FROM Orion.NodesCustomProperties ncp

          join (

          SELECT DISTINCT

          RUN,

          Count(*) AS SBUPD

          FROM Orion.NodesCustomProperties

              WHERE NOPD > '0'

              AND SB like 'SBU%'

          Group by RUN ) sbu on sbu.run=ncp.run

           

          where NOPD > '0'

          Group by RUN, sbu.SBUPD

          order by RUN

          • Re: multiple count statements in SWQL and columns
            tmcurry

            Thank you.

             

            That worked. We modifed it a bit as that is the building blocks for a much bigger query; that we are still working on.  A fe things to note

            1. WHERE NOPD > '0' we picked a better field to help with nulls then my originbal post (NodeID)

            2 We added a thrid colum, to prevent Nulls eliminating a RUN we used "left outer join instead" of join; of course mesverrum had no idea we were going further.

            3. once step 2 was done we made it so if a column had a null value it could change to zero changing

            ncp.RUN,

            Count(*) AS PD

            , sbu.SBUPD

            to

            ncp.RUN,

            Count(*) AS PD

            isnull (sbu.SBUPD,0)

            also we had third column, but you get the idea I hope.

             

            The last part we have to wourk out is RUN has the same names in multiple databases hence why building from EOC, we nee to get the duplicte lines out and add them.  That is the next part of the query we are working on.