Re: Simple sql question, I think.

On Jul 28, 6:20 pm, DaLoverhino <DaLoveRh...@xxxxxxxxxxx> 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?

.

Relevant Pages

  • Re: Q: an Inline SELECT forming a column
    ... i.e. the inline SELECT forms a column - I'm not sure whether the inline SQL ... SQL> select ename, empno, deptno, (select dname from dept where deptno ... Re-writing the query to use an in-line view instead: ... 381 bytes received via SQL*Net from client ...
    (comp.databases.oracle.server)
  • Re: GROUP BY
    ... I am trying to query a column that is not in my GROUP BY clause, ... SQL> select d.dname, e.deptno, sum ... from dept d, (select deptno, sum(sal) sumsal from emp group by ...
    (comp.databases.oracle.server)
  • Apologies to Oracle Corp.
    ... SQL> select * from emp; ... SQLRowCount returns -1 ...
    (comp.databases.oracle.server)
  • Re: Simple sql question, I think.
    ... SQL> select deptno, avgfrom emp group by deptno; ... Michel Cadot ...
    (comp.databases.oracle.server)
  • Getting strange result when using analytic function to calculate running sum.
    ... SQL> SELECT DEPTNO,ENAME,SAL ... PARTITION BY DEPTNO ... If I want to show 7875 for SCOTT's salary, how should I write the SQL? ...
    (comp.databases.oracle.misc)