SQL Server: basic functions (part 5) - Aggregation

Tweet

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:

• Count
• Sum
• Avg
• Min
• Max

And those are the functions we are going to see here.

Count

Count 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:

SELECT COUNT(*) as number_of_items FROM your_table

The above query will return the number of items in your_table, counting the null and duplicates values.

SELECT COUNT(ALL expression) as nonnull_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(DISTINCT expression) as unique_nonnull_items FROM your_table

will return the number of nonnull and unique items in your_table.

Sum

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

SELECT SUM(Price) as Total FROM your_table

will return the sum of all the prices in your_table. Null values are ignored.

Avg

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

SELECT AVG(Price) as avg_price FROM your_table

will return the average price from your_table. Null values are ignored.

Min

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

SELECT MIN(Price) as minimum_price FROM your_table

will return the minimum price from your_table.

Max

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

SELECT MAX(Price) as maximum_price FROM your_table

will return the highest price from your_table.

That's all for now. Don't forget to share your thoughts!