multiple count statements in SWQL and columns

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

Parents
  • 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.

Reply
  • 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.

Children
No Data