Re: Can it be done within one sql statement

correction:

select t.name, t.age, t.type, a.counttype from table1 t, (select type, count(*) counttype from table1 group by type) a
where t.type = a.type

"Dieter Buecherl" <Dieter.Buecherl_at_t-online.de> schrieb im Newsbeitrag news:a264ne$r9g$04$1_at_news.t-online.com...
> assuming that the column name is unique (else replace by pk):
>
> select t.name, t.age, t.type, a.counttype
> from table1 t, (select name, count(*) counttype from table1
> group by type) a
> where t.name = a.name
>
>
> "godmann" <allanwtham_at_yahoo.com> schrieb im Newsbeitrag
> news:95cd51c.0201162312.2b4eeadd_at_posting.google.com...
> > Hi,
> >
> > I have a case where a query result like this
> >
> > name age type
> > -----------------------
> > John 30 A
> > Peter 31 A
> > Andrew 29 B
> > James 25 B
> > Philip 27 B
> >
> >
> >
> > need to be transformed into a query result like this
> >
> >
> > name age type counttype
> > -------------------------------------
> > John 30 A 2
> > Peter 31 A 2
> > Andrew 29 B 3
> > James 25 B 3
> > Philip 27 B 3
> >
> > Note that counttype is 2 for both John and Peter have A (total A is 2)
> > and the rest have 3 (total B is 3)
> >
> > How is that possible in one single sql statement?? I bet PL/SQL need
to
> > be in place in order to achieve this!!
> >
> >
> > Allan W. Tham
> > DBA
>
>
Received on Thu Jan 17 2002 - 03:16:49 CST