||SQL Server Tips by Gama and Naughter
Aggregate functions compute one result from multiple rows. The most
common is the SUM function which returns the sum of the values from
a determined column.
There are two types of aggregate functions:
* Functions with numeric input - for example: SUM, AVG, STDEV,
STDEVP, VAR and VARP.
* Functions with alphanumeric input - for example: COUNT, COUNT_BIG,
MAX, MIN, BINARY_CHECKSUM, CHECKSUM and CHECKSUM_AGG.
Aggregate functions are legal in three situations:
* As a subquery or an outer query in a SELECT statement.
* As an expression in a HAVING clause.
* As an expression in a COMPUTE clause.
Aggregate functions ignore NULL values as input and return summary
data only. They are also deterministic because they always return
the same result for the same input.
Example of an aggregate function: A list of orders from database
Northwind, with the total cost for each order.
SUM(dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) AS
FROM dbo.[Order Details] INNER JOIN dbo.Orders ON dbo.[Order
Details].OrderID = dbo.Orders.OrderID
GROUP BY dbo.Orders.OrderID
Example of a row aggregate functions: A list of orders from database
Northwind, with the total cost for each order and a summary of all
dbo.[Order Details].UnitPrice*dbo.[Order Details].Quantity
FROM dbo.[Order Details] INNER JOIN
dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID
ORDER BY dbo.Orders.OrderID
COMPUTE SUM(UnitPrice*Quantity) BY dbo.Orders.OrderID
The above book excerpt is from:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter