Database Reference
In-Depth Information
The following example uses the
AVG()
function to return the average product
price offered by a specific vendor. It is the same
SELECT
statement used in the
previous example, but here the
DISTINCT
argument is used so the average only
takes into account unique prices:
▼
Input
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
▼
Output
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
▼
Analysis
As you can see, in this example
avg_price
is higher when
DISTINCT
is used
because there are multiple items with the same lower price. Excluding them
raises the average price.
Caution
Using
DISTINCT
With
COUNT()
DISTINCT
may only be used with
COUNT()
if
a column name is specified.
DISTINCT
may not be used with
COUNT(*)
, and so
COUNT(DISTINCT *)
is not allowed and generates an error. Similarly,
DISTINCT
must be used with a column name and not with a calculation or expression.
Tip
Using
DISTINCT
with
MIN()
and
MAX()
Although
DISTINCT
can technically
be used with
MIN()
and
MAX()
, there is actually no value in doing so. The minimum
and maximum values in a column are the same whether or not only distinct values are
included.
All the examples of aggregate functions used thus far have involved a single
function. But actually,
SELECT
statements may contain as few or as many aggre-
gate functions as needed. Look at this example:
