Re: simple query help

From: Gary Fowler <grfowler_at_mmm.com>
Date: Wed, 29 Dec 1999 14:26:20 -0700
Message-ID: <386A7C7C.3A3E0052@mmm.com>

I wouldn't necessarily call this a simple query. It's not to tough to get the count on the status by the dept as follows:

select dept, count(status) active
from dept_dude
where status = 'a'
group by dept;

This will give you the following output:

dept active
=== ====
north 4
east 2
south 2
west 1

Then you could do a similar query for inactive status.

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

Gabriel Millerd wrote:

> i have a table as follows (#1). i would like to have a report
> that gave the following (#2). can anyone help me?
>
> (#1)
>
> 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,
>
> (#2)
>
> dept active inactive
> ==== ====== ========
> north 4 2
> east 2 3
> south 2 0
> west 1 1
Received on Wed Dec 29 1999 - 15:26:20 CST