Re: Simple sql question, I think.

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 28 Jul 2007 20:04:14 +0200
Message-ID: <46ab851c$0$21519$426a34cc@news.free.fr>

"DaLoverhino" <DaLoveRhino_at_hotmail.com> a écrit dans le message de news: 1185643239.801685.287830_at_q75g2000hsh.googlegroups.com...
| 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.
|

select avg(avg(c)) from T group by a, b;

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

    DEPTNO AVG(SAL)
---------- ----------

        30 1566.66667
        20 2258.33333
        10 2916.66667

3 rows selected.

SQL> select avg(avg(sal)) from emp group by deptno; AVG(AVG(SAL))

---

   2247.22222

1 row selected.

Which is not the same than:

SQL> select avg(sal) from emp ;
  AVG(SAL)

---

2077.08333

1 row selected.

Regards
Michel Cadot Received on Sat Jul 28 2007 - 13:04:14 CDT