Re: simple query help

Try this:

select dept, sum(decode(status, 'a', 1, 0)) active, sum(decode(status, 'i', 1, 0)) inactive
from <table>
group by dept

Doug O'Leary wrote:

> 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 Tue Jan 04 2000 - 08:31:02 CST