Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Aggregate Functions
Aggregate functions always return a single value based on a set of supplied values and are always deterministic.
An aggregate function you're likely to use most frequently is COUNT. This function returns a count of rows based on certain criteria. If you specify a "*" as the criterion, COUNT returns the total number of rows in a table; for instance, the following query counts rows in a publishers table (please note that all examples are taken from query executions on my server and might not match results you will see on your server):
SELECT COUNT(*) FROM publishers
Results:
----------- 8
If you join multiple tables, COUNT(*) returns the number of rows satisfying the Join criterion:
SELECT COUNT(*) FROM publishers b INNER JOIN titles a ON a.pub_id = b.pub_id
Results:
----------- 18
Note that unlike other aggregate functions, COUNT does not ignore NULLs.
You often need to find the count of unique items within a column in a table. To do so, use COUNT (DISTINCT column_name) syntax. The following query counts unique titles within the sales table:
SELECT COUNT(DISTINCT title_id) FROM sales
Results:
----------- 16
A slight variation of the COUNT function is COUNT_BIG. Supported only in SQL Server 2000, COUNT_BIG returns a BIGINT data type. So if you suspect that you're about to count more than two billion rows, use COUNT_BIG; otherwise, COUNT suffices.
Perhaps the next most useful functions after COUNT are MAX and MIN. These do exactly what they say: They return the biggest or smallest values within a certain column. For instance, the following returns the greatest and smallest quantity from the sales table in the pubs database:
SELECT
MAX(qty) AS greatest,
MIN(qty) AS smallest
FROM sales
Results:
|
greatest |
smallest |
|
75 |
3 |
Note that the parameter for MIN and MAX can be any valid expression, including string columns, as in the following:
SELECT
MIN(title_id)
FROM sales
Results:
------ BU1032
On my server, I use a case-insensitive dictionary sort order, so the MIN function used on a string column orders the results alphabetically and return the smallest value. Keep in mind that string values returned by MIN and MAX depend on the collation settings on your server.