From m.vanrooy@sfs.nl Wed, 22 Aug 2001 07:05:12 -0700 From: "Rooy van, Marco" Date: Wed, 22 Aug 2001 07:05:12 -0700 Subject: RE: sql query Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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:oracledbam@hotmail.com] 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: oracledbam@hotmail.com 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: ListGuru@fatcity.com (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: m.vanrooy@sfs.nl 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: ListGuru@fatcity.com (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).