Aggregate arithmetic functions

1. Aggregate arithmetic functions

So, here we are at the last chapter of this course. You are almost done. In this chapter, you will learn about mathematical functions and how to apply them on data stored in SQL Server. A set of functions that come in handy when working with numeric data in SQL Server are functions performing calculations on groups of values. Some of the most important are: COUNT(), SUM(), MAX()/MIN(), AVG(). Let's take them one by one.

2. COUNT()

COUNT() is one of the most used functions in SQL Server. It returns the number of items found in a group. You can use it in three ways: with a parameter, prefixed by the word "ALL". The result is the number of all values from the expression. "ALL" serves as the default and it can be skipped from the function's parameter. Another option is to use a parameter prefixed by the word "DISTINCT". In this case, the result is the number of unique values from the group (excluding NULL values). The third option is to use only the * as a parameter, the result being the count of all rows from the query.

3. COUNT() example

This is the COUNT() function with all its flavors: the first two calls return the same value. When using "DISTINCT", the result is the number of unique values from a group. The star counts all rows from a query.

4. SUM()

Another well-known function is SUM(). It also has two flavors: you can use it with an expression prefixed by the optional word "ALL", the result being the sum of all values. When used with the word DISTINCT, only the unique values from the group are added.

5. SUM() example

Let's practice an example. This query retrieves information about the voters who voted 153 times. Four rows are returned. The query on the right side sums the values from the "total_votes" column. Notice that the first two calls of the function return identical results, ALL being an optional word: the result in all 4 rows are summed up. SUM() used with DISTINCT adds only the unique values, so the result of the last call is 153.

6. MAX() and MIN()

MAX() returns the maximum and MIN() returns the minimum value from a group. They can be used with the word DISTINCT in the parameter for taking into account only the unique values in a group. However, using DISTINCT doesn't really make any difference. The minimum value is the same, no matter how many identical values are in a group.

7. MAX() and MIN() example

The MIN/MAX() functions are simple and easy to use. This query returns the minimum recorded rating, as well as the maximum. You can see that the voters assigned both the minimum and the maximum possible score for some chocolate bars.

8. AVG()

AVG() returns the average of a group of values. You can use it with DISTINCT, similar to the other functions. When doing that, the average is calculated only for the unique values from the group. This is the AVG() function in action: the first result is the average of all values from the "rating" column. The second is the average of the unique ratings. The two averages are different.

9. Grouping data

When using these functions, keep in mind the following: if you want to apply them on groups of values (to calculate the average rating per each company, for example), you need to group your data with a GROUP BY statement, like in this query.

10. Let's practice!

By now, you are probably convinced that these functions are fun and easy. You will like using them even more after doing some practical examples on your own. Let's do that now.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.