Microsoft® SQL Server® 2012 Bible

250

Part II: Building Databases and Working with Data

diagram) occur following the FROM clause and the WHERE fi lters. This means that the data

can be assembled and fi ltered prior to being summarized without needing to use a subquery;

although, sometimes a subquery is still needed to build more complex aggregate queries.

Basic Aggregations

SQL includes a set of aggregate functions, listed in Table 10-1, which you can use as expres-

sions in the SELECT statement to return summary data.

TABLE 10 -1 Basic Aggregate Functions

Aggregate Function Data Type Supported Description

sum() Numeric Totals all the non-null values in

the column.

avg() Numeric Averages all the non-null values

in the column. The result has the

same data type as the input, so

the input is often converted to a

higher precision, such as

avg(cast col as float).

min() Numeric, string,

datetime

Returns the smallest number or

the fi rst datetime or the fi rst

string according to the current

collation from the column.

max() Numeric, string,

datetime

Returns the largest number or

the last datetime or the last

string according to the current

collation from the column.

Count[_big](*) Any data type

(row-based)

Performs a simple count of all

the rows in the result set up to

2 ,14 7, 4 8 3 , 6 4 7. T h e count_big()

variation uses the bigint data

type and can handle up to

2^63-1 rows.

Count[_big]([distinct]

column)

Any data type

(row-based)

Performs a simple count of all

the rows with non-null values in

the column in the result set up

t o 2 ,14 7, 4 8 3 , 6 4 7. T h e distinct

option eliminates duplicate

rows. Does not count blobs.

Var fl oat Returns the statistical variance

of non-null values of a numeric

expression.

c10.indd 250c10.indd 250 7/30/2012 4:26:12 PM7/30/2012 4:26:12 PM

http://www.it-ebooks.info