| |
 |
|
SQL Server Tips by Burleson |
Aggregate functions
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.
SELECT dbo.Orders.OrderID,
SUM(dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) AS
TotalCost
FROM dbo.[Order Details] INNER JOIN dbo.Orders ON dbo.[Order
Details].OrderID = dbo.Orders.OrderID
WHERE dbo.Orders.OrderID>'11075'
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 Details].UnitPrice*dbo.[Order Details].Quantity
FROM dbo.[Order Details] INNER JOIN
dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID
WHERE dbo.Orders.OrderID>'11075'
ORDER BY dbo.Orders.OrderID
COMPUTE SUM(UnitPrice*Quantity) BY dbo.Orders.OrderID
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
