Re: SQL question...

In article <memo.19980606155800.4101B_at_luked.compulink.co.uk>, luked_at_cix.co.uk (Luke Davies) wrote:

> In article <01bd8fc2$4e81d1c0$6383209a_at_gill>, marketing_at_force12.com
> (Force12 Solutions Ltd) wrote:
>
> > I realise this is more of an SQL question than an Oracle one but I'm
> > doing
> > it
> > on Oracle and so any Oracle specific functions can be used. I have
> > three
> > tables:
> >
> > SITE; Site_ID (NUMBER), Site_Name (VARCHAR2)
> >
> > REPORT_BATCH; Batch_ID (NUMBER), Batch_Name (VARCHAR2)
> >
> > REPORT_BATCH_MEMBERSHIP; Site_ID (NUMBER), Batch_ID (NUMBER)
> >
> > Each REPORT_BATCH can contain many SITE's and each SITE can be in
> > many REPORT_BATCH's. The REPORT_BATCH_MEMBERSHIP table contains
> > a record for each batch a site is a member of. The query I need will
> > return
> > a list
> > of all report batches (BATCH_ID, BATCH_NAME) and also an extra field
> > that
> > says
> > how many members (SITE's) that batch has. I think I have the concept
> > right
> > - Using
> > an OUTER JOIN, COUNT(*) and GROUP BY REPORT_BATCH.BATCH_ID but I can't
> > seem to crack it. Could anyone PLEASE help as this is driving me up
> > the
> > wall!
> > Thanks in advance, hope to hear from someone soon.....
> >
> > Alex Tait
> > Force12 Solutions Ltd
> > ajt_at_force12.com
> >
> >
> Is this what you need?
>
> SELECT rb.batch_id , rb.batch_name , count(rbm.site_id)
> FROM report_batch rb,
> report_batch_membership rbm
> WHERE rb.batch_id = rbm.batch_id
> GROUP BY 1,2
> /
>
> Luke
>

That was kak
Try this
SELECT rb.batch_id , rb.batch_name , count(distinct rbm.site_id) FROM report_batch rb,

     report_batch_membership rbm
WHERE rb.batch_id = rbm.batch_id
GROUP BY 1,2
/

That looks more what I meant

Luke Received on Sat Jun 06 1998 - 12:39:26 CDT