If think it isn't count but sum in the folowing statement:
select p.no, sum(decode(cat,'CHILD',1,0)) CHILD,
sum(decode(cat,'SPOUSE',1,0)) SPOUSE
from person p, relation r
where r.pers_no (+) =p.no
group by p.no
"cf" <news_at_sitinfo.com> a écrit dans le message de news:
3ba6f5a8$0$24009$4d4eb98e_at_read.news.fr.uu.net...
> select p.no, count(decode(cat,'CHILD',1,0)) CHILD,
> count(decode(cat,'SPOUSE',1,0)) SPOUSE
> from person p, relation r
> where r.pers_no (+) =p.no
> group by p.no
>
> Regards Christophe Flajolet.
>
>
> "Michel R." <mrochon_at_groupehba.com> a écrit dans le message news:
> Mysp7.1698$1M2.1015924_at_carnaval.risq.qc.ca...
> > Hello,
> >
> > I have 2 tables, the 1st one PERSON containing people descriptions and a
> 2nd
> > RELATION containing data about relationships between PERSONs (child,
> > spouse,...). I want to write a SQL statement to retrieve the number of
> > children and spouses for each person in PERSON. Here's what I have so
> far:
> >
> > select p.no, count(r.pers_no), count(r2.pers_no)
> > from person p, relation r, relation r2
> > where r.pers_no=p.no and r2.pers_no=p.no and r.cat = 'CHILD' and r2.cat
=
> > 'SPOUSE'
> > group by p.no
> >
> > This is not working as I would like, so I need a little help.
> >
> > Thank you all.
> >
> > MR
> >
> >
>
>
Received on Tue Sep 18 2001 - 05:48:12 CDT
