Re: simple query help

From: Gary Fowler <grfowler_at_mmm.com>
Date: Tue, 04 Jan 2000 09:39:43 -0700
Message-ID: <3872224F.BA4A0018@mmm.com>

I tried to post this last week, but apparently it didn't make it.

---

In order to get the results you are after, you have to dig a little deeper into the Oracle bag of tricks. This solution uses the DECODE function which is an Oracle extension that is not supplied by other SQL vendors. In other words it's non-standard:

select dept,

         count(decode(status, 'a', status, null)) active,
         count(decode(status, 'i', status, null)) inactive

from dept_dude
group by dept;

Happy SQLing,
Gary Fowler
3M Health Information Systems
grfowler_at_mmm.com

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 - 10:39:43 CST