Home > Articles > Data > SQL Server

  • Print
  • + Share This
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.

  • + Share This
  • 🔖 Save To Your Account