SQL Functions   «Prev  Next»

Lesson 9 SQL AVG function
Objective Understand the use of the AVG function.

SQL AVG Function

The AVG() function in SQL (Structured Query Language) is an essential aggregate function used for calculating the mean of a set of numeric values in a particular column. This function is invaluable across a variety of sectors and applications, due to the fundamental importance of averaging in data analysis and interpretation.
Here are several examples of AVG() function applications in various contexts, excluding worksheets and spreadsheets:
  1. Financial Services: In the banking and finance sector, the AVG() function can be employed to calculate average transaction values, average account balances, or average amounts of loans, providing useful insights for decision making and risk assessment.
  2. E-Commerce Platforms: Online marketplaces can use the AVG() function to calculate the average rating for a product or service, providing an aggregate indicator of customer satisfaction.
  3. Telecommunication Services: Telecom companies can use the AVG() function to compute the average call duration, data usage, or recharge amounts for their customer base, facilitating better resource planning and service provision.
  4. Healthcare Sector: In a hospital database, the AVG() function can calculate average patient stay duration, average treatment costs, or average recovery times for specific procedures. These insights can inform healthcare policy and practice.
  5. Education Sector: Educational institutions can utilize the AVG() function to compute average student scores, average class sizes, or average graduation rates, guiding strategic planning and resource allocation.
  6. Social Media Platforms: The AVG() function can be used to calculate the average number of likes, comments, or shares per post, aiding in the analysis of user engagement and content performance.
  7. Manufacturing Industry: Manufacturers can apply the AVG() function to find the average production time, average defect rate, or average downtime, driving process optimization and quality control.

These are just a few instances of how the AVG() function is used across different industries. By offering a means to compute the mean value of a data set directly within a SQL database, the AVG() function enables data-driven decision making and increases the efficiency of data processing operations.
If you have used worksheets and spreadsheets, you have probably used the AVG function, 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) 
FROM MyTable

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.

Listing 4.9

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)

Figure 4.10

AVG(price * 2)
AVG(sales) SUM(sales)
---------- ----------
title_id sales
-------- -------
T07 1500200
T05 201440