Generating statistics on a ternary mapping

I have a ternary mapping as follows

resource <-> resource_state <-> system

Each resource can be mapped to multiple systems. For each relationship there is a state that indicates the state of the resource on the system.

I want to generate a table that tells me how many systems are active and how many are passive. A system is active if it at least one resource has an "Online" state for the system. A system is passive if no resources have an "Online" state for the system.

Getting the number of active systems is pretty trivial. The problem I am having is generating the number of passive systems (which may also be trivial, but not yet to me ).

How many possible values for state can any one system have? Is it just "Online" or "Offline"? (IE boolean)

There are four states right now, but that could change down the road.

I did find a solution last night. The actual problem was slightly more complicated, where the resource and systems were contained in an application group. I wanted to create aggregates of the active systems and passive systems on the app group. I did this for "passive systems" with:

in a subselect.

Not sure that is the most optimal approach, but it works.

Figured I would post an even better solution I cam up with (in case someone might run into a similar problem). Basically, I get the total and the active systems and using subtraction get the passive systems. Way fewer lines of SQL and the performance is much better as well.