SQL Cookbook, 2nd Edition

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 efficient for day-to-day numeric chores. More importantly, as databases and datawarehouses supporting SQL probably remain the most common place to find an organization’s data, using SQL to explore and evaluate that data is essential for anyone putting that data to work. The techniques in this section have also been chosen to help data scientists decide which data is the most promising for further analysis.

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