select t1.userid, t1.superid, nvl(t2.count, 0) AS COUNT
from onetable t1,
(select superid, count(*) as count
from onetable
group by superid) t2
where t1.userid = t2.superid(+)
order by superid;
Best regards,
Marco
-----Oorspronkelijk bericht-----
Van: Seema Singh [mailto:[EMAIL PROTECTED]]
Verzonden: woensdag 22 augustus 2001 15:00
Aan: Multiple recipients of list ORACLE-L
Onderwerp: sql query
Hi
One table has following information
userid superid
14 -1
4 0
8 2
9 2
15 2
24 4
16 4
25 4
6 5
7 5
26 5
2 16
18 16
10 18
11 18
1 24
5 24
13 24
I want the output in following format
userid superid COUNT
14 -1 0
4 0 3
8 2 0
9 2 0
15 2 0
24 4 3
16 4 2
25 4 0
6 5 0
7 5 0
26 5 0
2 16 3
18 16 2
10 18 0
11 18 0
1 24 0
5 24 3
13 24 0
Where userid =xx , select count where Superid=xx
Please suggest the query.
THANKS
-Seema
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seema Singh
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rooy van, Marco
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).