SQWL to add a count column that doesn't break table

Hello,

New to SWQL and having trouble with the COUNT function. I have 200 custom groups that have various number of members. I am looking to create a table that contains the name (can do), custom properties (can do), and the count of elements within the specific group (where the trouble begins). Here is what I have so far:

SELECT TOP 1000 c.ContainerID, c.CustomProperties.GroupCity, c.CustomProperties.GroupState, COUNT(c.Members.DisplayName) AS DisplayNameCount
FROM Orion.Container c

If I remove the COUNT statement, it runs with all of my other data.

Once I get this, my next job will be to perform counts using various filters on the DisplayName....but one step at a time....

Any help is appreciated.

Find more posts tagged with

count

SWQL

orionsdk

Accepted answers

All comments

shuth

You need to tell it to group by the other fields, otherwise it is counting the total across all your groups.

This will show you all the combinations of ID/GroupCity/GroupState but should give you all the groups because the ContainerID will be unique.

SELECT TOP 1000 c.ContainerID, c.CustomProperties.GroupCity, c.CustomProperties.GroupState, COUNT(c.Members.DisplayName) AS DisplayNameCount
FROM Orion.Container c
GROUP BY c.ContainerID, c.CustomProperties.GroupCity, c.CustomProperties.GroupState

mrt3385

a millions thanks!!! this did work.

Two more questions if you please;

-How would I filter the count based on values in the name of the entry in the members table? For example, I have switches and routers named differently, and would like to get counts for each of those.

- I added some more columns to the select statement you posted. No problem. When I put in a WHERE clause, I get the error "mismatched input in 'WHERE' expecting 'EOF'.

Here is what I have:

SELECT TOP 1000 c.ContainerID, c.Name, c.CustomProperties.GroupCity, c.CustomProperties.GroupState, COUNT(c.Members.DisplayName) AS DisplayNameCount
FROM Orion.Container c
GROUP BY c.ContainerID, c.Name, c.CustomProperties.GroupCity, c.CustomProperties.GroupState
WHERE GroupCity LIKE 'Carlisle'

sum_giais

WHERE clauses need to go before GROUP BY clauses like so:

SELECT TOP 1000 
      c.ContainerID
     ,c.Name
     ,c.CustomProperties.GroupCity
     ,c.CustomProperties.GroupState
     ,COUNT(c.Members.DisplayName) AS [DisplayNameCount]
FROM Orion.Container AS c
WHERE c.CustomProperties.GroupCity LIKE '%Carlisle%'
GROUP BY c.ContainerID
        ,c.Name
        ,c.CustomProperties.GroupCity
        ,c.CustomProperties.GroupState