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

Get SQL Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.