The SQL Server SUM() function is one of the aggregate functions that calculates the total or sum of all the numeric records (or rows) selected. It accepts a single argument and sums up all the rows or values available in that column and returns the total. For instance, the SUM() function calculates the total sales, total revenue, expenditure, or order value, etc.
The SQL SUM() function is one of the most important functions while working with or evaluating the sales, profits, production, financial, or any other numerical calculations. As we mentioned earlier, if we use it on a single column in the SELECT statement, it returns a single result. However, it is commonly used in combination with the GROUP BY clause and an optional WHERE and HAVING clause for filters. If your SELECT statement has more than one column, you must use the GROUP BY clause to group the non-SUM() function columns. Apart from them, you can use the DISTINCT keyword inside the SUM() function to calculate the sum of the unique rows.
SQL SUM Function Syntax
The syntax of the SUM() function to find the total of numbers is as shown below.
SELECT SUM([Column_Name])
FROM [Source]
A bit complex, one uses the GROUP BY, WHERE, and HAVING clauses.
SELECT column1, SUM([ALL | DISTINCT] expression)
FROM [Source]
WHERE <condition>
GROUP BY column1
HAVING SUM(ColumnName) > expression
As it finds the total, we must use the SUM() function on numeric data types, such as INT, FLOAT, DECIMAL, MONEY, etc. If you apply it to a VARCHAR or any other string text, it will raise an error. The list of items that we can use in the SQL Server SUM() function is:
- SUM(column_name): Any numeric column that you want to find the total. Any column other than inside the SUM() should be placed either in GROUP BY or inside another Aggregate function.
- ALL: It is a default and applies to all columns.
- DISTINCT: To find the sum of unique rows.
- Source: Table name from where you retrieve data.
- Condition: It is optional. Before the Sum() aggregation happens, if you want to filter the data, use it.
- GROUP BY: Again, optional, but you must place non-aggregated columns (not inside the SUM()) within this clause.
Apart from the above simple ones, the following SQL SUM() function syntax is the most advanced and is used for analytics.
SUM([ALL] expression) OVER ([Partition_by] Order_by)
We will give a detailed explanation with a simple example to understand the OVER and PARTITION BY clauses.
To give you a context, if you want to find the total price of the products in your Store. If you’re going to find the full price of Black color products in your Store, you can use this one. The list of examples that we discuss on this page uses the AdventureWorksDW 2022 database tables.
Key thing to remember
- Always use the numeric columns, such as INTEGER, DECIMAL, FLOAT, etc columns as the SUM() function arguments. Otherwise, it will throw an error. For instance, SUM(FullName) will return an error.
- Any column that is not inside the SQL SUM() function should be mentioned in the GROUP BY clause.
- The columns that you selected must match the column placed in the GROUP BY.
- Use the WHERE clause to filter the data before the SUM() function is applied to rows.
- To filter the SUM() function data, use the HAVING clause because the WHERE clause won’t work.
- SUM() function automatically ignores NULL values.
- Use the DISTINCT keyword only when it is truly needed for a unique total.
- While using the SUM() function on large values, handling the decimal precision is very important. SO, use the ROUND() function before the SUM(). For instance, SELECT ROUND(SUM(Sales), 2)) AS TotalSale FROM Fact.
SQL SUM Function on One Column
As we mentioned earlier, the SUM() function returns the aggregate of the total number of records in the specified column. In this example, we will use the SUM() function to calculate the total sales. For this, we use the FactInternetSales table from the AdventureWorksDW 2022 database.
TIP: You must always use the ALIAS column to provide a meaningful name to the SUM() function output.
SELECT SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
The above query uses the SalesAmount as the SUM() function argument and calculates the sum of the sales in the InternetSales table. As it is the aggregated value, the result set would be a single value.
TotalSales
-----------
29358677.2207How to Find the Sum of Multiple Columns?
In SQL Server, you are not limited to using the SUM function on a single. We can use it on multiple columns in a single SELECT statement to find the total of multiple values. In this example, we find the sum of sales, total product Sales, and orders.
SELECT SUM(TotalProductCost) AS Cost,
SUM(SalesAmount) AS Sale,
SUM(OrderQuantity) AS Orders
FROM FactInternetSales
The result is:
Cost Sale Orders
---------------- ---------------- --------
17277793.5757 29358677.2207 60398SQL SUM() Function and GROUP BY
In the previous examples, we used the SUM() function to find the aggregated total of a single column. In real-time, we have to calculate the sum of numeric values within a group. However, if you try to combine the aggregated and non-aggregated columns in the SELECT statement, then the Server will throw an error. To deal with this error, you must use the GROUP BY Clause to combine those non-aggregated columns.
In most cases, we usually use this sum function to find the total product sales belonging to a particular category or color. The following are some of the instances where we use the SUM() and GROUP BY combination.
- Sum of sales in a product category and subcategory.
- Total sales in each country, region, state, etc.
- Finding the Yearly, Monthly, or Quarterly Sales.
The following query uses the YEAR() function to get year numbers from the Order date, and the GROUP BY clause will group the dates by year. Next, the SUM() function finds the total sales that happened in each year.
SELECT YEAR(OrderDate) AS Year, SUM(SalesAmount) AS Sales
FROM FactInternetSales
GROUP BY YEAR(OrderDate)
Error and Fix it
While working with the SQL Server SUM() function, one of the most common errors was not mentioning the non-aggregated column in the GROUP BY. For instance, the below query has the Year column, which is not mentioned in the grouping. So, it will throw an error. To fix this error, add the last line from the above query.
SELECT YEAR(OrderDate) AS Year, SUM(SalesAmount) AS Sales
FROM FactInternetSales
Error: Column 'FactInternetSales.OrderDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.Using SUM() and JOINS
Apart from working with a single table, you can use any SQL JOIN to calculate the aggregated SUM() from multiple tables. It is very helpful, and we can provide greater information about the result set. For instance, calculating the sum of sales by territory group. To do so, we need a table with sales information and another table with territorial details. In such a case, use the JOINs to join those two tables and use the SQL SUM() function to calculate the total sales by group.
SELECT [SalesTerritoryGroup], SUM(SalesAmount) AS Sales
FROM [DimSalesTerritory] st JOIN FactInternetSales fs
ON st.SalesTerritoryKey = fs.SalesTerritoryKey
GROUP BY [SalesTerritoryGroup]
As we explained in detail in the GROUP BY article, you can use a single or multiple values while performing the SUM() function. The query below calculates the sum of the sales and the orders by territory group and country.
SELECT [SalesTerritoryGroup], [SalesTerritoryCountry],
SUM(SalesAmount) AS Sales, SUM(OrderQuantity) AS Orders
FROM [DimSalesTerritory] st JOIN FactInternetSales fs
ON st.SalesTerritoryKey = fs.SalesTerritoryKey
GROUP BY [SalesTerritoryGroup], [SalesTerritoryCountry]
Using the SQL SUM() Function with DISTINCT
By default, the SUM() function uses ALL as the default one and finds the total of all the rows in a given column. However, you can explicitly specify the DISTINCT Keyword to get the total of unique records in a column. It first deletes the duplicate rows before the SUM() function calculates the total.
In the following query, we used the normal and the DISTINCT SUM() to show the difference between the two while calculating the total. Here, the DISTINCT SalesAmount calculates the sum of the Unique rows present in a FactInternetSales table whose values are NOT NULL (Ignores the NULL Records).
SELECT SUM(SalesAmount) AS Sale, SUM(DISTINCT SalesAmount) AS UniqueSale
FROM FactInternetSales
The result is:
| Sale | UniqueSale |
| 29358677.2207 | 36097.586 |
Let me give you another example of the DISTINCT SUM() function with grouping so that you can see the group by regular and unique row calculations. The query below groups rows by the year and finds the sum of all the sales records in each year group. And also the distinct sales in each group whose values are NOT NULL.
SELECT YEAR(OrderDate) AS Year, SUM(SalesAmount) AS Sale,
SUM(DISTINCT SalesAmount) AS UniqueSale
FROM FactInternetSales
GROUP BY YEAR(OrderDate)
TIP: ON top of the above query, you can use the WHERE clause to restrict or filter the DISTINCT SUM().
SQL SUM() function with HAVING Clause
The regular WHERE clause won’t work on aggregated SUM() data, so we must use the HAVING clause. When you use the HAVING clause with the SUM() function, it applies filters on the aggregated total. The following query groups the Fact table by the ProductKey. The HAVING clause checks whether the aggregated amount (the sum of the sales amount) returned by the SUM() function is greater than 1000000 or not. If this is True, the corresponding records will display.
SELECT ProductKey, SUM(SalesAmount) AS Sale
FROM FactInternetSales
GROUP BY ProductKey
HAVING SUM(SalesAmount) > 1000000
SQL Server SUM Function WHERE Clause
The aggregated SUM() function can be combined with the WHERE clause to filter the data. If you use WHERE clause, it filters the rows before applying the SUM() function. By this, you can omit the unwanted columns from SUM() function to calculate the total. For instance, calculating the total product sale except the Black, White, and Blue color products.
The following query uses the WHERE Clause along with the SUM() function to display the sum of the sales amount in each SalesTerritoryRegion except northeast, southeast, and Germany. If you need further filters on aggregated data, you can uncomment the HAVING Clause (last line) to display the total orders greater than 6000.
SELECT [SalesTerritoryRegion],
SUM(SalesAmount) AS Sales, SUM(OrderQuantity) AS Orders
FROM [DimSalesTerritory] st JOIN FactInternetSales fs
ON st.SalesTerritoryKey = fs.SalesTerritoryKey
WHERE [SalesTerritoryRegion] NOT IN ('Northeast', 'Southeast', 'Germany')
GROUP BY [SalesTerritoryRegion]
--HAVING SUM(OrderQuantity) > 6000
Using SUM with ORDER BY Clause
You can use this SQL Server SUM function in the ORDER BY clause. For example, the following query will find the total orders, Sales grouped by the Region column. Next, the ORDER BY Clause will order those query results based on the sum of the sales amount in descending. Here, the ORDER BY recognizes the ALIAS column name, so we used it.
SELECT [SalesTerritoryRegion],
SUM(SalesAmount) AS Sales, SUM(OrderQuantity) AS Orders
FROM [DimSalesTerritory] st JOIN FactInternetSales fs
ON st.SalesTerritoryKey = fs.SalesTerritoryKey
WHERE [SalesTerritoryRegion] NOT IN ('Northeast', 'Southeast', 'Germany')
GROUP BY [SalesTerritoryRegion]
HAVING SUM(OrderQuantity) > 6000
ORDER BY Sales DESC
--ORDER BY SUM(SalesAmount) DESC
Using SUM() with Expression
The SQL Server SUM() function also allows you to use any expression or mathematical calculation as the argument. For instance, if you have the total orders and the unit cost, you can multiply both of them to get the actual sales. On top of that, use the SUM() function to calculate the total revenue generated by the condition.
Imagine we don’t have the tax value in our table, and we want to calculate the total sales, including the tax as the result. Let us assume the Tax rate is 8%. The following query calculates the sales amount with an 8% discount and adds it to the original sales value. Next, the SUM() uses the whole date to calculate the total revenue.
SELECT YEAR(OrderDate) Year, SUM(SalesAmount) AS Sales,
SUM(SalesAmount * 0.08) AS Tax,
SUM(SalesAmount + SalesAmount * 0.08) AS Total
FROM FactInternetSales
GROUP BY YEAR(OrderDate)
ORDER BY Year
Apart from the general mathematical calculations, the SUM() function allows you to use multiple columns inside it. To combine those columns, you can use any of the Arithmetic operators to perform the calculation inside the SUM() function.
The query below will find the profit by subtracting the product cost from the sales amount within the SUM() function.
SELECT [SalesTerritoryCountry], SUM(TotalProductCost) AS Cost,
SUM(SalesAmount) AS Sales
,SUM(SalesAmount - TotalProductCost) AS Profit
FROM [DimSalesTerritory] st JOIN FactInternetSales fs
ON st.SalesTerritoryKey = fs.SalesTerritoryKey
GROUP BY [SalesTerritoryCountry]
SQL SUM() function with a Subquery
Whether you use the WHERE or HAVING clauses in the above examples, we are trying to test expressions against one or two values. When you use the combination of the SUM() function and the subquery, you can perform more complex filters on the normal and aggregated data.
The SELECT statement in the query below selects the FirstName, LastName, and YearlyIncome columns from the DimCustomer table. The WHERE clause uses the subquery to find the average income of whole customers. Next, it checks every record to see whether the customer’s income is greater than the average income. If true, it returns the records.
SELECT FirstName, LastName, YearlyIncome
FROM DimCustomer
WHERE YearlyIncome > (SELECT AVG(YearlyIncome) FROM DimCustomer)
Similarly, you can use the SUM, SUBQUERY, and HAVING clause combination to check whether the aggregated sum of the sales is greater than the average sales of a customer.
SELECT FirstName, LastName, SUM(SalesAmount) AS Sales
FROM DimCustomer JOIN FactInternetSales ON
DimCustomer.CustomerKey = FactInternetSales.CustomerKey
GROUP BY FirstName, LastName
HAVING SUM(SalesAmount) > (SELECT AVG(SalesAmount) FROM FactInternetSales)
Using SUM() and the EXISTS operator
The combination of the EXISTS operator and the SUM() functions gives more flexibility to perform the most difficult and complex search operations on the table. The following example query returns the sum of the sales amount from the fact table for all the customers who were born below the year 1980.
SELECT SUM(SalesAmount) [1980Sale] FROM FactInternetSales f
WHERE EXISTS (
SELECT 1 FROM DimCustomer c
WHERE c.CustomerKey = f.CustomerKey
AND YEAR(c.BirthDate) > 1980)
The result set is:
1980Sale
---------------
4290192.0754Using SUM() and COUNT()
Some people may confuse the SUM() and COUNT() functions and consider both of them to be the same. They both are aggregate functions, but they are completely different and perform different operations. The following list shows the basic difference between the SQL Server SUM() and COUNT() functions.
| SUM() | COUNT() |
|---|---|
| It finds the total or sum of numeric values. | It counts the total number of rows. |
| It ignores the NULL values. | If you mention the column name instead of *, it ignores NULLs. |
| The SUM() function only works on numeric columns. | COUNT() works on any column and finds the number of rows. |
| SELECT SUM(Sales) FROM products | SELECT COUNT(orderID) FROM products |
| To find the total sales, the sum of salaries, etc. | To count total employees, orders, entries, etc. |
Let me give an example for a better understanding of SUM() Vs. COUNT(). The SUM() function in the query below will find the sum of the yearly income of the customers. Next, the COUNT() function returns the total customers.
SELECT SUM(YearlyIncome) AS Income, COUNT(CustomerKey) AS Customers
FROM DimCustomer
Income
----------
1059240000.00Similarly, you can use the GROUP BY clause to find the sum and total number of customers who belong to the Male and Female.
SELECT Gender, SUM(YearlyIncome) AS Income, COUNT(CustomerKey) AS Customers
FROM DimCustomer
GROUP BY Gender
The result is:
| Gender | Income | Customers |
| F | 523960000.00 | 9133 |
| M | 535280000.00 | 9351 |
Calculating Cumulative SUM (Running Total)
A cumulative sum is nothing but a running total, i.e., the sum of all previous rows available in the given column from start to end. It is a very helpful metric to analyze the money flow in each step.
The following query uses the SQL SUM() function and OVER clause to calculate the running total or cumulative sum of the list price ordered by the Product Key. Here, we used the IS NOT NULL to remove the NULL values from the result.
SELECT [ProductKey],[EnglishProductName],[Color],[ListPrice]
,SUM(ListPrice) OVER(ORDER BY ProductKey) AS RunningTot
FROM [DimProduct]
WHERE ListPrice IS NOT NULL
If you use the PARTITION BY clause, the running total will reset its value foreach partition. Here, the value will reset for each product color.
SELECT [ProductKey],[EnglishProductName],[Color],[ListPrice]
,SUM(ListPrice) OVER(PARTITION BY Color ORDER BY ProductKey) AS RunningTot
FROM [DimProduct]
WHERE ListPrice IS NOT NULL
How to Calculate the Rolling SUM?
Unlike the running total, rolling SUM allows you to specify the number of preceding rows to use for the calculation. It is very useful to identify the last week, 10 or 15 days’ expenditure, sales, orders, etc.
The following SQL SUM() function query calculates the rolling sum of the list price of the last seven days (including the current row) of products. It means the present row + the previous six days’ cost.
SELECT [ProductKey],[EnglishProductName],[Color], [ListPrice], StartDate
,SUM(ListPrice) OVER(ORDER BY StartDate ROWS
BETWEEN 7 PRECEDING AND CURRENT ROW) AS RollingSum
FROM [DimProduct]
WHERE ListPrice IS NOT NULL
SQL SUM() Function Handling NULL values
By default, the SUM() function ignores the NULL values and finds the sum of the total rows in a given column. So, while using the SUM() function, you don’t have to do anything to handle NULL values. However, if you want to explicitly handle those items, use the COALESCE function.
Imagine you have the product table with the following records.
| Items | Quantity |
| Laptops | 100 |
| Mobiles | 500 |
| Printers | NULL |
| Desktops | 600 |
As you can there are no prints in the stock, and it has the NULL value. If you use the SUM() function to calculate the total quantity, it will return 1200.
SELECT SUM(Quantity) AS Total
FROM Product
If you want to replace the NULL value with zero, you can try the COALESCE function as shown in the query below.
SELECT SUM(COALESCE(Quantity, 0)) AS Total
FROM Product
The result of both queries will return the same result, but if your table has many records and grouping is applied, all the NULL values are shown as zeros.
TIP: The SUM() function treats NULL values as it is and returns NULL as the output. To avoid the same, use the above two options.