| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: simple query help
Hi;
I got four answers three of which worked (listed below). Thanks for the help.
Working queries:
(1)
select dept, sum(decode(status,'a',1,0)) active,
sum(decode(status,'i',1,0)) inactive
from <table>
group by dept;
(2)
select dept, count(decode(status,'a',status,null)) active
count(decode(status,'i',status,null)) inactive
from <table>
group by dept;
(3)
select a.dept, a.active, nvl(b.inactive,0) 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(+)
As a bit of a follow up; it seems to me that the third query would be much more inefficient in a complex multi-table query. From what I can see, it's executing three select statements for the one that's done in the first two. Also, it seems to me that the first two queries are shades of each other and there shouldn't be significant difference between them even as the level of complexity and data rises. How close am I on those observations and are there anymore that anyone would like to share?
Thanks again for the help; I've learned a lot by lurking in this group. Anybody have any UNIX questions, I'd be happy to return the favor.
Doug
--
 |
 |