Conclusion


Aggregates are powerful tools for organizing and interpreting data. SQL Server provides a wide array of common aggregates for you to use, and you can apply them in various flavors by adding keywords to the aggregates. If exactly the right aggregate isn't already built into SQL Server, you can easily create your own aggregate function through Visual Studio, then call your custom-designed aggregate through a SQL query.

Chapter 5 Quick Reference

To

Do this

Count the records in a table

SELECT COUNT(*) FROM <Table_Name>

Count the number of records with values that are not null in a field

SELECT COUNT (<Field_Name>)

FROM <Table_name>

Count the records that meet a specified condition

SELECT COUNT (*)

FROM <Table_Name>

WHERE <condition>

Count the records with the same value in one of the fields

SELECT <Field_Name>, COUNT(*)

FROM <Table_Name>

GROUP BY <Field_Name>

Sum the values contained in a column

SELECT SUM(<Field_name>)

FROM <Table_Name>

Obtain the minimum value in a column

SELECT MIN(<Field_Name>)

FROM <Table_Name>

Obtain the maximum value in a column

SELECT MAX(<Field_Name>)

FROM <Table_Name>

Obtain the average for the values in a column

SELECT AVG(<Field_Name>)

FROM <Table_Name>

Obtain subtotals and grand totals for the values

SELECT <Field_Name>, <FUNCTION_NAME>(<Field_Name>)

FROM <Table_Name>

GROUP BY <Field_Name> WITH ROLLUP

Obtain results only for values that are not repeated

SELECT <FUNCTION_NAME> (DISTINCT <Field_Name>)

FROM <Table_Name>

Define your own aggregate function

Create a CLR aggregate function using Visual Studio





Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net