More of this Feature • Part 1: Sum and Average • Part 3: Max and Min

Join the Discussion "Need some assistance or advice?  Join us in the About Databases forum." Mike Chapple

SQL provides the COUNT function to retrieve the number of records in a table that meet given criteria.  We can use the COUNT(*) syntax alone to retrieve the number of rows in a table.  Alternatively, a WHERE clause can be included to restrict the counting to specific records.

For example, suppose our Widgets product manager would like to know how many orders our company processed that requested over 100 widgets.

Here's the SQL query:

SELECT COUNT(*) AS 'Number of Large Orders'
FROM WidgetOrders
WHERE Quantity > 100

And the results:

Number of Large Orders 
---------------------- 
3

The COUNT function also allows for the use of the DISTINCT keyword and an expression to count the number of times a unique value for the expression appears in the target data.  Similarly, the ALL keyword returns the total number of times the expression is satisfied, without worrying about unique values.  For example, our product manager would like a simple query that returned the number of unique continents in our orders database.

First, let's take a look at the use of the ALL keyword:

SELECT COUNT(ALL Continent) As 'Number of Continents'
FROM WidgetOrders

And the result set:

Number of Continents 
-------------------- 
7

Obviously, this is not the desired results.  If you recall the contents of the WidgetOrders table from the previous page, all of our orders came from North America, Africa and Europe.  Let's try the DISTINCT keyword instead:

SELECT COUNT(DISTINCT Continent) As 'Number of Continents'
FROM WidgetOrders

And the output:

Number of Continents 
-------------------- 
3

That's more like it!

In the next section of this article, we'll look at the functions used to find the maximum and minimum values of an expression.  Read on!

Next page > MAX() and MIN() > Page 1, 2, 3