Re: Can it be done within one sql statement

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:13:30 CST