Re: question on 10g analytic functions

On Jul 22, 5:50 pm, GuangMei <zl..._at_hotmail.com> wrote:
> This is a question on 10g analytic functions
>
> I have Table T1 with four number columns:
>
> Table T1:
> id      X       Y       Z
> 1       0       0       0
> 1       0       1       0
> 2       0       1       1
> 2       1       1       1
> 3       0       0       1
> 3       0       1       1
> 3       1       0       1
>
> After group by "id:, I get:
>
> For X=0:
> id      Count1
> 1       0
> 2       1
> 3       1
>
> For Y=0:
> id      Count2
> 1       1
> 2       2
> 3       1
>
> For Z=0:
> id      Count3
> 1       0
> 2       2
> 3       3
>
> I want to create a new table T1_CNT which shows:
>
> id      Count1  Count2  Count3
> 1       0       1       0
> 2       1       2       2
> 3       1       1       3
>
> I can do these in multiple steps. But I would like to create T1_CNT
> from T1 directly, maybe using some 10g analytic functions.
> Any idea?
>
> TIA.
> Guang

From your example, I dont think an analytic function is required:

create table t1_cnt as
select id, sum(x) as count1, sum(y) as count2, sum(z) as count3 from t1
group by id; Received on Tue Jul 22 2008 - 21:18:21 CDT