Re: Simple sql question, I think.

On Jul 28, 6:20 pm, DaLoverhino <DaLoveRh..._at_hotmail.com> wrote:
> Is there a way to take the average of a group of averages in a sql
> statement?
>
> So for instance:
>
> select a, b, avg( c) from T group by a, b;
>
> -- That finds the average of each group a,b.
>
> Now how to take the average of those averages? I can create a view I
> suppose, but I'm curious to know if there was a way to do it without
> resorting to making a view?
>
> thanks.

Just curious about what that statistic would represent.

SQL> col avg(sal) format 999G999D00
SQL> select count(*), avg(sal) from emp;

  COUNT(*) AVG(SAL)
---------- -----------

        14 2,073.21

1 row selected.

SQL> col AVG(AVG_SAL) like avg(sal)

SQL> select avg(avg_sal) from
  2 ( select avg(sal) avg_sal from emp group by deptno );

AVG(AVG_SAL)

    2,219.44

1 row selected.

(or just for fun...)

SQL> select deptno, count(*), avg(sal) from emp group by rollup(deptno)

    DEPTNO COUNT(*) AVG(SAL)
---------- ---------- -----------

        10          3    2,916.67
        20          5    2,175.00
        30          6    1,566.67
                   14    2,073.21

4 rows selected.

SQL> comp avg of avg(sal) on report
SQL> break on report

SQL> select deptno, count(*), avg(sal) from emp group by deptno;

    DEPTNO COUNT(*) AVG(SAL)
---------- ---------- -----------

        30          6    1,566.67
        20          5    2,175.00
        10          3    2,916.67
                      -----------
avg                      2,219.44

3 rows selected.

What does that 2,219.44 give you? Received on Sun Jul 29 2007 - 04:38:05 CDT