How to use SUM Function in SQL Server

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.

SUM Function in SQL Server Sales Table

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;
SUM Function in SQL Server

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.

SUM Function in SQL Server with ALL and DISTINCT with Product_Sales Table

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;
SUM Function in SQL Server with ALL option

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;
SUM Function in SQL Server with DISTINCT option

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.

SUM Function in SQL Server with More than One Column MonthlySales Table

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;
SUM Function in SQL Server with More than One Column

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.

SUM Function in SQL Server with GROUP BY clause Sales_By_Region Table

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;
SUM Function in SQL Server with GROUP BY clause

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;
SUM Function in SQL Server with HAVING clause

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: