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.
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 |