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


Do this

Count the records in a table


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


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>


Obtain results only for values that are not repeated


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

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: