Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: AVG across columns

Re: AVG across columns

From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Mon, 6 Aug 2007 12:00:58 +1000
Message-ID: <MPG.21212be4e6552c17989995@news.readfreenews.net>


DA Morgan says...

> > Given this data in table x:
> > row_id v1 v2 v3 v4
> > A 5 10 null 9
> > B null 5 5 null
> > C 6 8 7 7
> >
> >
> > I'd like a query result that looks like:
> > row_id row_avg
> > A 8 -- (5+10+9)/3
> > B 5 -- (5+5)/2
> > C 7 -- (6+8+7+7)/4
> >
> > The simple math of (sum(v1 + v2 + v3 + v4) / 4) won't work due to null
> > addition, and nvl to sidestep this gives an incorrect denominator count.
> >
> > Solution needs to be SQL not PL/SQL.
> >
> > Yes, I know the data is poorly normalized.
> >
> > GM
>
> No.

Bummer, as I suspected but I thought I'd ask, just in case ..

> And poorly doesn't even begin to describe it. <g>

Data is from a third-party <expletive> app. Looks like my only option within straight SQL is to "pseudo-normalize"/"de-case" the data using a series of UNION ALL joins.

Unfortunately not very efficient, and the real data is more complex, about 30 cols, to be grouped into "sets" for averaging purposes.

select row_id, col_group, avg(col_val) from (

    select row_id, 'group_1' col_group, v1 col_val     from stupid_table
    union all
    select row_id, 'group_1' col_group, v2 col_val     from stupid_table
    union all
    select row_id, 'group_1' col_group, v3 col_val     from stupid_table
    union all
    select row_id, 'group_1' col_group, v4 col_val     from stupid_table
    union all
    select row_id, 'group_2' col_group, w5 col_val     from stupid_table
    union all
    select row_id, 'group_2' col_group, w6 col_val     from stupid_table
    .......
    )
group by row_id, col_group;

:-(

Any better alternatives appreciated.

GM Received on Sun Aug 05 2007 - 21:00:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US