Re: Can it be done within one sql statement

This looks like a case where you could use an analytic function, using notation like

...count(*) over...

However, the OVER() clause baffles me -- never found a complete accounting of what's allowed -- so I'm afraid to actually try it. :-)

Looking through some source where I've gotten it to work in the past, I'd guess either

...count(*) over()...
or
...count(*) over(partition by type)...

Might need a recent release for syntax like this to be recognized.

John

On 16 Jan 2002 23:12:09 -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

--
Got an Oracle database question?
Try the search engine for the database docs at:
http://tahiti.oracle.com/