Publishers of technology books, eBooks, and videos for creative people

Home > Articles

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

Finding 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:

  • Type:

    AVG(expr)

    expr is a column name, literal, or numeric expression. The result’s data type is at least as precise as the most precise data type used in expr.

Listing 6.4 and Figure 6.4 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 average and total sales for business books; both calculations are null (not zero), because the table contains no business books. The third query uses a subquery (see Chapter 8) to list the books with above-average sales.

Listing 6.4 Some AVG() queries. See Figure 6.4 for the results.

SELECT AVG(price * 2) AS "AVG(price * 2)"
FROM titles;
SELECT AVG(sales) AS "AVG(sales)",
SUM(sales) AS "SUM(sales)"
FROM titles
WHERE type = 'business';
SELECT title_id, sales
FROM titles
WHERE sales >
(SELECT AVG(sales) FROM titles)
ORDER BY sales DESC;

Figure 6.4Figure 6.4 Results of Listing 6.4.


TIPS

  • AVG() works with only numeric data types.

  • The average of no rows is null—not zero, as you might expect.

  • If you’ve used, say, 0 or –1 instead of null to represent missing values, the inclusion of those numbers in AVG() calculations yields an incorrect result. Use NULLIF() to convert the missing-value numbers to nulls, so they’ll be excluded from calculations; see “Comparing Expressions with NULLIF()” in Chapter 5.

  • MySQL 4.0 and earlier versions lack subquery support and won’t run the third query in Listing 6.4.

  • + Share This
  • 🔖 Save To Your Account