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
/
Gabriel Millerd <millerd_at_ns1.rli-net.net> wrote in message
news:84do8m$blc$1_at_news.powerisp.com...
>
> 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 Sat Jan 01 2000 - 13:18:15 CST
