Sams Teach Yourself SQL in 21 Days, Fourth Edition

Using Aggregate Functions with Subqueries

The aggregate functions SUM, COUNT, MIN, MAX, and AVG all return a single value. To find the average amount of an order, type this:

						SELECT AVG(O.QUANTITY * P.PRICE)
						FROM ORDERS O, PART P
						WHERE O.PARTNUM = P.PARTNUM

AVG
===========

    2419.16

mysql> SELECT AVG(O.QUANTITY * P.PRICE)
    -> FROM ORDERS O, PART P
    -> WHERE O.PARTNUM = P.PARTNUM
    -> ;
+---------------------------+
| AVG(O.QUANTITY * P.PRICE) |
+---------------------------+
|               2447.133333 |
+---------------------------+
1 row in set (0.03 sec)

This statement returns only one value. To find out which orders were above average, use the preceding SELECT ...