More of this Feature • Part 1: Sum and Average • Part 2: Counting Records

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