Database Reference
In-Depth Information
▼
Input
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
▼
Output
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+
▼
Analysis
This
SELECT
statement differs from the previous one only in that this one con-
tains a
WHERE
clause. The
WHERE
clause filters only products with a
vend_id
of
1003
, and, therefore, the value returned in
avg_price
is the average of just that
vendor's products.
Caution
Individual Columns Only
AVG()
may only be used to determine the average of a spe-
cific numeric column, and that column name must be specified as the function param-
eter. To obtain the average value of multiple columns, multiple
AVG()
functions must
be used.
Note
NULL
Values Column rows containing
NULL
values are ignored by the
AVG()
function.
COUNT()
does just that: It counts. Using
COUNT()
, you can determine the num-
ber of rows in a table or the number of rows that match a specific criterion.
COUNT()
can be used two ways:
■
Use
COUNT(*)
to count the number of rows in a table, whether col-
umns contain values or
NULL
values.
■
Use
COUNT(column)
to count the number of rows that have values in a
specific column, ignoring
NULL
values.
This first example returns the total number of customers in the
customers
table:
