29
Learn SQL: Microsoft SQL Server - Episode 7: Aggregate data with SUM and AVG
In this episode we are going to explore two additional functions we can utilise to aggregate our data, namely SUM and AVERAGE (AVG).
As the name suggests SUM allows us to calculate the sum of the value in a given column. AVERAGE allows us to calculate the average value of all the values in a specified column.
Let's get practical and see how this works in SSMS.
First off, let's open a new query window in the AdventureWorks database. We will be using the Sales.SalesOrderHeader table. we will be using SUM to calculate the sum of the Subtotal column, which is the total order values.
Here we have the SUM of all the orders in the Sales.SalesOrderHeader table.
If for instance we need to calculate the SUM of the totals by the CustomerID or SalesPersonID, then we just need to specify this in our query statement. In our example we will use the CustomerID.
We use the Group By function to tell SQL by which column we want to calculate the total by, let’s provide an Alias for the SUM function and call it Total Order Value.
Voila! we now see for each customer their total order value.
Next we will look at the AVERAGE function, which works similar to SUM. There we will be copying the SUM query statement and pasting it below itself.
The only changes we will make is instead of SUM we replace it with AVG, this is the Average. We will also change the Alias to Average Order Value.
Awesome, we can read the Average order value for each customer. We can even combine these two statements into one.
Here we have the CustomerID, the Total Order Value of each customer as well as their Average Order Value. We can confirm these numbers by supplying a Count function in our query statement.
Let’s see an example of this in SSMS.
As we can see the numbers add up correctly!
Thats all she wrote, this is how we can calculate the SUM and Average. Remember to keep practicing and to eat your vegetables. Signing off for now, see you next time...
29