| Lesson 9 || SQL |
| Objective || Understand the use of the |
SQL AVG Function
If you have used worksheets and spreadsheets, you have probably used the
which allows you to average the values in a given column. This works exactly the same in SQL, where the
AVG function lets you average the numeric values contained within a given column. The syntax is identical to the
SUM function. You simply call it and enclose in parentheses the name of the column you want to average:
SELECT MyName, MyAddress, MyNumber, AVG(MyNumber)
This returns the different columns requested, plus one additional column, unnamed, that contains the average value for the MyNumber values.
Calculating an Average with AVG()
Use the aggregate function AVG() to find the average, or arithmetic mean, of a set of values. The arithmetic mean is the sum of a set of quantities divided by the number of quantities in the set. To calculate the average of a set of values:
expr is a column name, literal, or numeric expression.
The results data type is at least as precise as the most precise data type used in expr.
Listing 4.9 and Listing 4.10 shows some queries that involve AVG().
The first query returns the average price of all books if prices were doubled.
The second query returns the 1) average and 2) total sales for business books; both calculations are null (not zero),
because the table contains no business books.
The third query uses a subquery to list the books with above-average sales.
Listing 4.9 Some AVG() queries. See Figure 4.9 for the results.
SELECT AVG(price * 2) AS "AVG(price * 2)"
SELECT AVG(sales) AS "AVG(sales)",
SUM(sales) AS "SUM(sales)"
WHERE type = 'business';
SELECT title_id, sales
WHERE sales >
(SELECT AVG(sales) FROM titles)
ORDER BY sales DESC;
AVG(price * 2)