Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: simple query help

Re: simple query help

From: Doug O'Leary <dkoleary_at_mediaone.net>
Date: Tue, 4 Jan 2000 19:02:11 -0600
Message-ID: <MPG.12dc540bf6b08e02989706@nntp.ce.mediaone.net>


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

--



Douglas K. O'Leary
Senior System Admin
dkoleary_at_mediaone.net
Received on Tue Jan 04 2000 - 19:02:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US