Using CAST() and AVG() Functions to Return Numeric Values

I want to write a query to the pubs database's sales table that for each store returns the average sales quantity, and I need the data to be accurate in numeric data type to two decimal places. The quantity is stored in the integer column qty. When I use the CAST() and the AVG() functions to return the averages of the values, the functions return zeros after the decimal point. Here's the query I wrote:

SELECT stor_id, CAST(AVG(qty)AS numeric(12,2)) AS avg_qtyFROM salesGROUP BY stor_id

Although the statement converts the values to the numeric data type, I receive the results that Figure 2 shows. Specifically, in stor_id 7066 the result of averaging quantities 50 and 75 should be 62.50 and not 62.00. How can I write a query that returns results to two decimal places?

Instead of converting the result of the AVG() function to numeric (12,2), you need to convert the qty column inside the AVG() function. This change is necessary because the AVG() aggregate function returns a value of the same data type family as its argument. In your example, the argument is the qty column, which has an integer data type. You can perform the conversion in two ways—explicitly by using the CAST() function:

SELECT stor_id, AVG(CAST(qty AS numeric(12,2))) AS avg_qtyFROM salesGROUP BY stor_id

or implicitly by multiplying the qty column by 1. (read "one dot"):

SELECT stor_id, AVG(1. * qty) AS avg_qtyFROM salesGROUP BY stor_id

Note that although the AVG() function returns a value of the same data type as its argument family, the value might not be expressed with the same degree of accuracy as the argument. For example, the earlier explicit and implicit conversions return a result to 6 decimal places, as Figure 3 shows. You can cast the result of the AVG() function to 2 decimal places by explicitly using the CAST function as follows:

SELECT stor_id, CAST(AVG(1. * qty) AS numeric(12, 2)) AS avg_qtyFROM salesGROUP BY stor_id

This statement returns the desired result, which Figure 4 shows. See also, "Using the POWER() Function with a Negative Exponent."