Re: SQL Group By and Question

From: Peter Connolly <peter_at_wpi.edu>
Date: 2000/05/08
Message-ID: <Pine.OSF.4.21.0005081354530.20945-100000@wpi.WPI.EDU>#1/1

Actaully, I don't think a connect by is necessary since we are only looking for the top level. Try this:

SELECT

       t1.cat_id,
       sum(decode(t2.parent_cat_id,null,0,1)) -- Add 0 if we don't join
                                              -- Else 1 if we do
  FROM
       table t1, --Top level
       table t2  --Children
 WHERE t1.cat_id = t2.parent_cat_id (+)  
   AND t1.parent_cat_id is null               -- Only look at top level

 GROUP BY t1.cat_id

-Peter

On Mon, 8 May 2000, Bob Fazio wrote:

> Yes,
> Look at connect by and start with in the docs. That should get you where
> you need to go.
> If I had more time I would get the syntax, but it is well documented.
>
> --
> Robert Fazio, Oracle DBA
> rfazio_at_home.com
> remove nospam from reply address
> http://24.8.218.197/
> "Sean" <dolans_at_my-deja.com> wrote in message
> news:8f6qel$g3q$1_at_nnrp1.deja.com...
> > I don't know if a "smooth" SQL statement will do this or if I have to
> > build the resultset in PL/SQL, but here is the data:
> >
> > CAT_ID PARENT_CAT_ID DISPLAY
> > --------- ------------- --------------------
> > 49999999 Miscellaneous
> > 5 49999999 Sub under Misc, #1
> > 6 49999999 Sub under Misc, #2
> > 7 Same level as Misc
> >
> > Question : I would like to know the top level cat_id and a count of
> > how many level 2s there are underneath it: something like:
> >
> > CAT_ID COUNT
> > 49999999 2
> > 7 0
> >
> > Thanks,
> > Sean
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>
>
Received on Mon May 08 2000 - 00:00:00 CDT