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

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

tmcurry over 5 years ago

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.

SELECT DISTINCT

RUN,

Count(*) AS PD

FROM Orion.NodesCustomProperties

where NOPD > '0'

Group by RUN

order by RUN

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

0 tmcurry over 5 years ago

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.
Cancel
Vote Up 0 Vote Down

Cancel

Reply

0 tmcurry over 5 years ago

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.
Cancel
Vote Up 0 Vote Down

Cancel

Children

No Data