SQL Cookbook

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.

Tip

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

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 ...