Selecting counts of values in the same column for each entity

I have two tables, application_group and resource_group. An application_group may have multiple resource_groups. Each resource_group has its own state.

Visually, this looks like:

application_group
-----------------------
id VARCHAR(255)
display_name VARCHAR(255)

resource_group
--------------------
id VARCHAR(255)
app_group_id VARCHAR(255)
state VARCHAR(255)

Now, resource_group has a finite number of states. What I would like is a result set like so:

application_group.id
application_group.display_name
count(resource_groups where state = 'Online')
count(resource_groups where state = 'Offline')
count(resource_groups where state = 'Faulted')

There would be one row for each application_group.id. I can't quite seem to get this to work though. I've been working with nested queries, but I always get 1 row with all of the totals.

I have three questions:

- Is there a way to prepare the kind of result set I am looking for?
- Is it expensive? (I can always test this one if I can get the first issue worked out)
- Without putting state counts in the application_groups table, is there a better way to do this?

I don't want to put the counts in the applications_group table, because it will require me to maintain that state and it is no longer normalized.

Any help is appreciated!

- Is there a way to prepare the kind of result set I am looking for?

yes


- Is it expensive? (I can always test this one if I can get the first issue worked out)

expensive??


- Without putting state counts in the application_groups table, is there a better way to do this?

you almost had it yourself:


rudy
http://r937.com/

Thanks Rudy! I did end up solving it earlier today, but it a much less elegant way. I ended up having something like 6 joins!

I tried the syntax you gave me in HSQLDB, which is the DB I have been using for dev testing, but it doesn't like it. Time to switch to something a little more functional.

Thanks again!