cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

multiple count statements in SWQL and columns

Jump to solution

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

0 Kudos
1 Solution

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

- Marc Netterfield, Github

View solution in original post

0 Kudos
2 Replies
Level 9

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.

0 Kudos

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

- Marc Netterfield, Github

View solution in original post

0 Kudos