In this SQL Server tutorial, I will explain the **SUM function in SQL Server** to add all the values of the given set of values.

You will be introduced to the syntax of the **SUM()** function and its definition. Then, you will understand with a simple example how to use the SUM() function with table columns.

After that, you will learn how to use the **SUM()** function with **GROUP BY**, **HAVING**, and **more than one column**.

### SUM Function in SQL Server

**The SUM() function in SQL Server adds all the values of the given set of values; in other words, if you have a column of type integer in your tables and want to compute the sum of all the values in that column, then use the SUM() function.**

The SUM() is an aggregate function that works on the data group; the syntax is below.

`SUM ( [ ALL | DISTINCT ] expression )`

Where,

**SUM():**The function computes the sum of the given data set.**expression:**The expression can be the columns, set of values, or any numeric set of data. But it can’t be bit type data type; remember, as an expression, you can’t use the aggregate function or subqueries.**ALL | DISTINCT:**When you use the SUM() function with different options, such as ALL or DISTINCT, it computes the sum of all the values differently.**SUM(ALL expression):**When you use the SUM() function with the ALL option, then it applies the SUM() function on all the values of the given expression (columns values), including the duplicated values in the columns of the table.**SUM(DISTINCT expression):**This one only applies the SUM() function on the unique values of the columns while excluding the duplicate ones.

Let’s understand the workings of the SUM() function with an example. Suppose you work at a retail store and can access the Sales table below.

As you can see in the **Sales** tables, the column **SalesPrice** shows the sale price of each product. Now you want to compute the total sale price, so use the below query.

```
SELECT SUM(SalesPrice) AS TotalSalePrice
FROM Sales;
```

Applying the SUM() function on the SalesPrice column using the above query returns the sum of all the values in that column. As a result, 15466.4545 dollars that you can see in the output.

If you separately add the values of the SalesPrice column, at the end, you will end up with the same value that you have computed using the SUM() function.

### SUM Function in SQL Server with ALL and DISTINCT

When used with the SUM() function, the ALL or DISTINCT returns the different sum values. First, let’s see how to use the ALL option within the SUM() function to apply the sum function to all the values in a column, including duplicate ones.

The syntax is given below.

```
SELECT SUM(ALL column_name)
FROM table_name;
```

For example, you have a Product_Sales table, which is shown below.

As the SalePrice column contains the duplicate sale price (values such as 59999.99), compute the total sale price with the ALL option within the SUM() function. So use the below query.

```
SELECT SUM(ALL SalePrice) AS TotalSalePrice
FROM Product_Sales;
```

When you have used the ALL option with the SUM() function, it returns the result as a 3019999.90 dollar sale price, including the duplicate values. Here, the SUM() function is applied to all the values of the column SalePrice.

Next, let’s use the **DISTINCT **option within the SUM() function to compute the sum of all the unique values in a column.

The syntax is given below.

```
SELECT SUM(DISTINCT column_name)
FROM table_name;
```

For that, use the below query.

```
SELECT SUM(DISTINCT SalePrice) AS TotalSalePrice
FROM Product_Sales;
```

Here, this time, it returns the result 2959999.91, but it is not equal to the result you have got with option ALL; as you know, the SUM() function computes the sum of only the unique values.

The **SalePrice** column contains duplicate values, such as **59999.99**, but the SUM() only considers the unique value and ignores the duplicate values, so you get the result as **2959999.91**.

### SUM Function in SQL Server with More than One Column

So, if you want to use the SUM() function with more than one column of the same table, then you are free to use it, or it can be used with any number of table columns.

For example, suppose you have access to the **MonthlySales** table shown below.

The table contains the three columns, **JanuarySale**, **FebruarySales,** and **MarchSales**. Now you have to compute the total sales for each month. For that, use the below query.

```
SELECT SUM(JanuarySales) AS JanTotalSales, SUM(FebruarySales) AS FebTotalSales,
SUM(MarchSales) AS MarchTotalSales
FROM MonthlySales;
```

As you can see in the above output, the SUM() function is applied to the three columns **JanuarySale**, **FebruarySales,** and **MarchSales**, and it returns the sum of total sales in each month separately, which you can see in the above output.

The total sales for January are 6600.00, February is 7000.00, and March is 7800.00. Here, you need to understand that you can use the SUM() function with more than one column of the tables.

This is how to use the SUM function in SQL Server with more than one column of a table.

### SUM Function in SQL Server with GROUP BY clause

The GROUP BY clause groups the data according to the specified column, so here, you can compute the sum of values in a column and group those values based on the specific column.

You have a **Sales_By_Region** table with columns **SaleID**, **Region**, and SaleAmount, shown below.

So the table contains the sale amount according to the regions such as North, East, West, and South.

Comput the total sale amount based on the regions using the below query.

```
SELECT Region, SUM(SaleAmount) AS TotalSales
FROM Sales_By_Region
GROUP BY Region;
```

After executing the above query, it returns the total sales by region, such as total sales in the **east at 6650.00** dollars, north at **4750.00** dollars, south **at 4150.00** dollars, and **west at 3940.00** dollars.

Look at the **GROUP BY Region** query; total sales are computed per region using the SUM() function. So first, the values from the **Sales_By_Region** are grouped by the Region column, which means it aggregates the data in distinct groups, each representing a unique region.

Next, after grouping the data according to the unique regions, the SUM() function is applied to each group to compute the total sales separately for each created group.

As a result, you can see separate total sales for the **East**, **West**, **North**, and **South** regions.

This is how to use the SUM function in SQL Server with GROUP BY clause.

### SUM Function in SQL Server with HAVING clause

As you know, the HAVING clause allows you to filter groups of databases on aggregated functions. So, you can use the SUM() function as a condition with the HAVING clause to filter the group-based data.

For example, use the same Sales_By_Region table you used in the above section. So here, you need to compute the total sales amount for each region, but only for regions with more than 4700 dollars.

So, for that, use the below query.

```
SELECT Region, SUM(SaleAmount) AS TotalSales
FROM Sales_By_Region
GROUP BY Region
HAVING SUM(SaleAmount) > 4700.00;
```

From the above output, only two regions, East and North, made sales greater than 4700.00 dollars.

Here, the **GROUP BY Region** query groups the sales amount by region, **SUM(SaleAmount) AS TotalSales** computes the total sales amount for each region, and **HAVING SUM(SaleAmount)** filters out the groups where the total sales are greater than 4700 dollars.

You can use the SUM function in SQL Server with the HAVING clause.

## Conclusion

In this SQL Server tutorial, you computed the sum of all the values in a table column using the SUM() function in SQL Server, a kind of aggregated function. Also applied the SUM() function on the multiple columns of the same table and calculated the sum of the values of columns using the GROUP BY and HAVING clause.

You may like to read:

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.