Re: Can I do it in a single SQL statement?

On 16 Jan 2002 23:08:00 -0800, allanwtham_at_yahoo.com (godmann) wrote:

>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
select name, age,a.type,b. counttype
from person
,
(select type, count(*) counttype
 from person
 group by type) b
where b.type = a.type

Too often people revert to procedural 'solutions'

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Jan 17 2002 - 11:48:00 CST