Re: Can it be done within one sql statement

From: <gquesnel_at_magma.ca>
Date: Mon, 04 Feb 2002 04:47:45 GMT
Message-ID: <3c5e0f7f.29735477@news.magma.ca>

You can do it by doing a select to create your count, and then a select to show the full result
something like...

Select mt.name,mt.age,mt.type, tc.type_cnt from mytab mt, (select type,count(*) type_cnt

                            from mytab group by type) tc

where mt.type = tc.type;

Guy.

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
Received on Sun Feb 03 2002 - 22:47:45 CST