||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
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 orders.
SELECT dbo.Orders.OrderID, dbo.[Order
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