Learn SQL: Microsoft SQL Server - Episode 7: Aggregate data with SUM and AVG
Tags: sql, database, beginners, challenge
Published: 2021-11-04 20:54:11

![wear-your-mask-people](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g0e3oadwtdm45cs2gfd6.png)

In this episode we are going to explore two additional functions we can utilise to aggregate our data, namely **SUM** and **AVERAGE** _(AVG)_.

![skid-8](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kbhyvlec17czxyb3ytu7.gif)

As the name suggests **SUM** allows us to calculate the <u>sum</u> of the value in a given column. **AVERAGE** allows us to calculate the <u>average</u> value of all the values in a specified column.

Let's get practical and see how this works in <abbr title="SQL Management Studio">SSMS</abbr>.

![skid-2-cover](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i9qnc79j09mzjbdmzax1.png)

###Calculate SUM

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 <u>sum</u> of the **Subtotal** column, which is the total order values.

![practical-1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/670t2n6cw91iz29ysvdn.gif)

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

![skid-5](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1na8lzhuc9nxb6ysehfa.gif)

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 <u>Total Order Value</u>.

![practical-2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j5exkt065agew1qfz0bp.gif)

>Voila! we now see for each customer their total order value.

![skid-2-cover](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i9qnc79j09mzjbdmzax1.png)

###Calculate AVG

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. 

![skid-8](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kbhyvlec17czxyb3ytu7.gif)

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 <u>Average Order Value</u>.

![practical-3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9axmxckz2rqdnv3akfne.gif)

Awesome, we can read the Average order value for each customer. We can even combine these two statements into one.

![practical-4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/84vesy6yhswsgznl9z27.gif)

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.

![skid-5](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1na8lzhuc9nxb6ysehfa.gif)

Let’s see an example of this in <abbr title="SQL Management Studio">SSMS</abbr>.

![practical-5](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m947eg6lt09lluhzn46j.gif)

>As we can see the numbers add up correctly!

![skid-2-cover](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i9qnc79j09mzjbdmzax1.png)

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

![medidation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mujtb8rb496e3uu1nqsc.gif)