SWQL for status count in multiple columns from same table

I think this should work for you:

SELECT
COUNT (CASE Status WHEN '1' THEN 1 else NULL end) AS UP,
COUNT (CASE Status WHEN '2' THEN 1 else NULL end) AS DOWN,
COUNT (Status) as Total
FROM ORION.Nodes

Sorry, just saw your custom property.  Try this and fill in your custom property where applicable:

SELECT
{Your Custom_Property},
COUNT (CASE Status WHEN '1' THEN 1 else NULL end) AS UP,
COUNT (CASE Status WHEN '2' THEN 1 else NULL end) AS DOWN
FROM ORION.Nodes
JOIN {Custom Property Table} ON nodes.nodeid = {custompropertytable}.nodeid
WHERE {Custom_Property} = 'Server' OR {Custom_Property} = 'Network'
GROUP BY {Custom_Property}

I guess, I'm doing something wrong or gave the wrong info at first.

your provided sql not working.

So, here is the thing, two tables used.

1- SELECT NodeID, Status FROM Orion.Nodes

2- SELECT ISP_Provider, NodeID FROM Orion.NodesCustomProperties

will make filter, only for Status = '1' AND Status = '2'  AND ISP_Provider IS NOT NULL

required output to show how many nodes are up and down per ISP. Need this to use in modern dashboard.

thanks

Ok, your first request was a little different.  I just tested the below script on my database and got a good result.  FYI... you do not need to SELECT nodeid, just include it in your join statement.

SELECT
ISP_Provider,
COUNT (CASE Status WHEN '1' THEN 1 else NULL end) AS UP,
COUNT (CASE Status WHEN '2' THEN 1 else NULL end) AS DOWN
FROM ORION.Nodes
JOIN Orion.NodesCustomProperties ON nodes.nodeid = NodesCustomProperties.nodeid
WHERE ISP_Provider <> 'NULL'
GROUP BY ISP_Provider

Thanks Bubowski.