In the past several Jump Start columns, I've been looking into different aspects of the SELECT statement. Understanding and mastering the SELECT statement is the most important skill for developing database applications. In this issue, I explain how you can make the SELECT statement count.
The SQL SELECT statement's COUNT function can tell you the number of rows in a table, how many unique values are in a column, and the number of unique values for an expression. In its simplest form, COUNT(*) takes no column parameters and returns the number of rows in a table. For example, the following code returns the total number of rows in the Employees table in the Northwind database:
SELECT COUNT(*) FROM Employees
You can combine the COUNT function with the DISTINCT argument to return the number of unique values in a particular column. For example, to return the number of unique titles from the Employees table, you can use the query
SELECT COUNT(DISTINCT Title) FROM Employees
You can use multiple instances of the COUNT function to get the number of unique values in multiple columns. The following example retrieves the number of unique titles and countries:
SELECT COUNT(DISTINCT Title), COUNT(DISTINCT Country) FROM Employees
Remember, COUNT is an aggregated function and can't be combined with nonaggregates, such as standard column names. However, it can be combined with other aggregate functions. For example, the query
SELECT COUNT(DISTINCT OrderID) as \[Total Orders\],
AVG(UnitPrice) as \[Average Price\]
FROM \[Order Details\]
returns the total number of orders and the average unit price from the Order Details table.