Aggregate functions are used in SQL Server queries to perform operation on a group of values and returns one single value as result. Aggregate functions are used with the SELECT statement, with the GROUP BY and the HAVING clauses.
There are different aggregate functions, however the most important are:
CountCount is the only aggregate function that considers NULL values. All the other functions ignore NULL values. That is something to take into account because it may produce unexpected results.
The Count function returns the number of items in a group. It can be used with the ALL or the DISTINCT arguments. Examples are:
The above query will return the number of items in your_table, counting the null and duplicates values.
SELECT COUNT(*) as number_of_items FROM your_table
will return the number of nonnull items in your_table. Expression is the name of a column in your_table.
SELECT COUNT(ALL expression) as nonnull_items FROM your_table
will return the number of nonnull and unique items in your_table.
SELECT COUNT(DISTINCT expression) as unique_nonnull_items FROM your_table
SumThe Sum function is used to return the sum of values. It can sum only numeric values, while null values are ignored.
As the Count function, Sum has the ALL (default) or the DISTINCT arguments.
will return the sum of all the prices in your_table. Null values are ignored.
SELECT SUM(Price) as Total FROM your_table
AvgSimilar to the Sum function, Avg will return the average of values. Again you can specify the ALL or the DISTINCT arguments (ALL is the default).
will return the average price from your_table. Null values are ignored.
SELECT AVG(Price) as avg_price FROM your_table
MinThe Min function will return the lowest value in a group of items. Null values are ignored and you can specify the ALL (default) or DISTINCT arguments. In this case - as you may have noticed - the DISTINCT argument is completely useless (a minimum value is minimum regardless the fact that it is unique).
will return the minimum price from your_table.
SELECT MIN(Price) as minimum_price FROM your_table
MaxOpposite to the Min function, Max will return the highest value in a group of items. Again, null values are ignored and you can use the ALL or the DISTINCT arguments. As in the Min function, DISTINCT is useless for the same reason.
will return the highest price from your_table.
SELECT MAX(Price) as maximum_price FROM your_table
That's all for now. Don't forget to share your thoughts!