DEV Community

Cover image for Learn SQL: Microsoft SQL Server - Episode 7: Aggregate data with SUM and AVG
Goran Kortjie
Goran Kortjie

Posted on

4 1

Learn SQL: Microsoft SQL Server - Episode 7: Aggregate data with SUM and AVG

wear-your-mask-people

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

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.

skid-2-cover

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

practical-1

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

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.

practical-2

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

skid-2-cover

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

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.

practical-3

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

practical-4

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

Let’s see an example of this in SSMS.

practical-5

As we can see the numbers add up correctly!

skid-2-cover

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

Heroku

Built for developers, by developers.

Whether you're building a simple prototype or a business-critical product, Heroku's fully-managed platform gives you the simplest path to delivering apps quickly — using the tools and languages you already love!

Learn More

Top comments (0)

Image of PulumiUP 2025

Let's talk about the current state of cloud and IaC, platform engineering, and security.

Dive into the stories and experiences of innovators and experts, from Startup Founders to Industry Leaders at PulumiUP 2025.

Register Now

👋 Kindness is contagious

Engage with a wealth of insights in this thoughtful article, valued within the supportive DEV Community. Coders of every background are welcome to join in and add to our collective wisdom.

A sincere "thank you" often brightens someone’s day. Share your gratitude in the comments below!

On DEV, the act of sharing knowledge eases our journey and fortifies our community ties. Found value in this? A quick thank you to the author can make a significant impact.

Okay