Recipe7.1.Computing an Average

Recipe 7.1. Computing an Average

Problem

You want to compute the average value in a column, either for all rows in a table or for some subset of rows. For example, you might want to find the average salary for all employees as well as the average salary for each department.

Solution

When computing the average of all employee salaries, simply apply the AVG function to the column containing those salaries. By excluding a WHERE clause, the average is computed against all non-NULL values:

  1 select avg(sal) as avg_sal 2   from emp    AVG_SAL ---------- 2073.21429

To compute the average salary for each department, use the GROUP BY clause to create a group corresponding to each department:

  1 select deptno, avg(sal) as avg_sal 2   from emp 3  group by deptno     DEPTNO     AVG_SAL ----------  ----------         10  2916.66667         20        2175         30  1566.66667

Discussion

When finding an average where the whole table is the group or window, simply apply the AVG function to the column you are interested in without using the GROUP BY clause. It is important to realize that the function AVG ignores NULLs. The effect of NULL values being ignored can be seen here:

 create table t2(sal integer) insert into t2 values (10) insert into t2 values (20) insert into t2 values (null)  select avg(sal)    select distinct 30/2   from t2            from t2   AVG(SAL)               30/2 ----------         ----------         15                 15  select avg(coalesce(sal,0))    select distinct 30/3   from t2                        from t2    AVG(COALESCE(SAL,0))                 30/3 --------------------           ----------                   10                   10

The COALESCE function will return the first non-NULL value found in the list of values that you pass. When NULL SAL values are converted to zero, the average changes. When invoking aggregate functions, always give thought to how you want NULLs handled.

The second part of the solution uses GROUP BY (line 3) to divide employee records into groups based on department affiliation. GROUP BY automatically causes aggregate functions such as AVG to execute and return a result for each group. In this example, AVG would execute once for each department-based group of employee records.

It is not necessary, by the way, to include GROUP BY columns in your select list. For example:

  select avg(sal)   from emp  group by deptno   AVG(SAL) ---------- 2916.66667       2175 1566.66667

You are still grouping by DEPTNO even though it is not in the SELECT clause. Including the column you are grouping by in the SELECT clause often improves readability, but is not mandatory. It is mandatory, however, to avoid placing columns in your SELECT list that are not also in your GROUP BY clause.