Re: AVG across columns

From: William Robertson <williamr2019_at_googlemail.com>
Date: Wed, 08 Aug 2007 23:38:28 -0700
Message-ID: <1186641508.676660.271220@b79g2000hse.googlegroups.com>

On Aug 6, 3:00 am, Geoff Muldoon <geoff.muld..._at_trap.gmail.com> wrote:
> 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

Or just

SELECT id, AVG(column_value)
FROM stupid_table

     , TABLE(number_tt(v1, v2, v3, v4))
GROUP BY id
ORDER BY id;

(Substitute your friendly local collection type for "number_tt".)

e.g:

SQL> WITH stupid_table AS

  2      ( SELECT 'C' id, 6 v1, 8 v2, 7 v3, 7 v4 FROM dual
  3        UNION ALL SELECT 'A', 5, 10, NULL, 9 FROM dual
  4        UNION ALL SELECT 'B',NULL,5,5,NULL FROM dual )

  5 SELECT id, AVG(column_value)
  6 FROM stupid_table
  7 , TABLE(number_tt(v1, v2, v3, v4))   8 GROUP BY id
  9 ORDER BY id;

I AVG(COLUMN_VALUE)

- -----------------
A                 8
B                 5
C                 7

3 rows selected. Received on Thu Aug 09 2007 - 01:38:28 CDT