Chapter 7. Working with Numbers
This chapter focuses on common operations involving numbers, including numeric computations. While SQL is not typically considered the first choice for complex computations, it is very efficient for day-to-day numeric chores.
Some recipes in this chapter make use of aggregate functions and the GROUP BY clause. If you are not familiar with grouping, please read at least the first major section, called “Grouping,” in Appendix A.
7.1. Computing an Average
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.
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 empAVG_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 deptnoDEPTNO AVG_SAL ---------- ---------- 10 2916.66667 20 2175 30 1566.66667
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 ...