|
|
 |
|
|
Join the Discussion
|
"Need
some assistance or advice? Join us in the
About Databases forum."
Mike
Chapple
|
|
|
|
|
|
In this final segment of our aggregate functions feature
article, we'll look at the functionality SQL provides to locate the records
containing the smallest and largest values for a given expression. The
MAX() function returns the largest value in a given data series. We can
provide the function with a field name to return the largest value for a given
field in a table. MAX() can also be used with expressions and GROUP BY
clauses for enhanced functionality. Once again, we'll use the
WidgetOrders example table for this query (see the first
page of this article for the specification and contents). Suppose our
product manager wanted to find the order in our database that produced the most
revenue for the company. We could use the following query to find the
order with the largest total dollar value:
SELECT
MAX(Quantity * UnitPrice)As 'Largest Order'
FROM WidgetOrders
Our results would look like this: Largest Order
---------------------
2517.58 The MIN() function functions in the same manner, but returns
the minimum value for the expression. Let's try a slightly more
complicated example utilizing the MIN() function. Our sales department is
currently analyzing data on small widget orders. They'd like us to
retrieve information on the smallest widget order placed on each
continent. This requires the use of the MIN() function on a computed value
and a GROUP BY clause to summarize data by continent. Here's the
SQL:
SELECT
Continent, MIN(Quantity * UnitPrice) AS 'Smallest Order'
FROM WidgetOrders
GROUP BY Continent
And our result set: Continent
Smallest Order
------------- ---------------------
Africa 167.04
Europe 2099.02
North America 70.65 That's it for this week. If you're not clear
on the use of aggregate functions, be sure to stop
by our forum for some assistance. Check back each week for coverage of
a new databases topic. If you'd like a weekly reminder by e-mail, be sure
to subscribe to
our newsletter. Next page >
> Page 1,
2, 3
|
| 