This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

  • 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

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