Re: simple query help

From: Doug O'Leary <dkoleary_at_mediaone.net>
Date: Mon, 3 Jan 2000 21:00:38 -0600
Message-ID: <MPG.12db1e55610a89e7989704@nntp.ce.mediaone.net>

Hi;

Just to review: The single table looks like:

dept dude status
==== ==== ======

north, bob, i,
north, jim, a,
north, sam, i,
north, jeff, a,
north, mary, a,
north, sal, a,
east, mary, a,
east, dwaine, i,
east, tom, i,

east, bill, i,
east, chuck, a,
south, matt, a,
south, holly, a,
west, joy, i,
west, stacy, a,

And the desired report looks like:

dept active inactive
==== ====== ========
north 4 2
east 2 3
south 2 0
west 1 1

The proposed answer was:

select a.dept, a.active, b.inactive
from
(select dept, count(*) active from <table> where status = 'a' group by
dept) a,
(select dept, count(*) inactive from <table> where status = 'i' group by
dept) b
where
a.dept = b.dept
/

I'm just learning sql, so even getting this far is pretty impressive. I tried coming up with the answer with my version of personal Oracle and wasn't getting anywhere. When I plugged in the solution, though, the result is:

DEPT ACTIVE INACTIVE
---------- --------- ---------

east               2         3
north              4         2
west               1         1

No south dept which doesn't have any inactive 'dudes'.

I tried a couple of things with decode and nvl; however, I'm still not getting anywhere with it. I can't seem to get around the fact that the query for south/inactive returns no rows, hence, the entire south dept is skipped.

I've heard of the concept of an outer join - don't know how to do one yet, but I've heard of it. Is that what's required here?

Thanks!

Doug O'Leary

Douglas K. O'Leary
Senior System Admin
dkoleary_at_mediaone.net

Received on Mon Jan 03 2000 - 21:00:38 CST