Today, I will describe the SQL SUM () function along with its use cases in this article. There are various mathematical calculations we need to do in day-to-day business requirements. SQL Server offers various system functions which I have listed below to get these mathematical calculations easily by using them. One such requirement is to get the sum of values stored in a numeric column. We can use SQL Server system function SUM () to easily get the sum of the values stored in a numeric column of the table.
The SQL SUM function is an aggregate function that is used to perform a calculation on a set of values from a specified expression and return a single value in their output. There are additional aggregate functions as well in SQL Server which I have listed below for your reference:
- APPROX_COUNT_DISTINCT
- AVG
- CHECKSUM_AGG
- COUNT
- COUNT_BIG
- GROUPING
- GROUPING_ID
- MAX
- MIN
- SUM
- STRING_AGG
- VAR
This article is intended for the SQL SUM () function so let’s start discussing this in this article. The SQL SUM() function returns the sum of all values or the sum of only values specified through conditional expressions. This function will ignore NULL values present in the columns. The syntax of this function is given in the below statement.
|
1 2 3 4 |
--Syntax SUM ( [ ALL | DISTINCT ] expression ) |
Here,
- ALL means all values will be considered to get the result. It is the default argument and if you will not specify any argument then SQL Server considers it as ALL and return the result accordingly
- DISTINCT as its name suggests, SQL Server only considers unique values to return the result and duplicate values will be ignored
- Expression is a column, function, or any combination of the arithmetic, bitwise, and string operators
Next, I will provide you with the details about my source table on which I will demonstrate all use cases of the SQL SUM function.
Source Table
I have created a new table named Sales in my test database TESTDB for this demonstration. Table Sales has 5 columns to store sales-related data. The name of these columns is ProductName, Price, Quantity, InvoiceMonth, and City. Two columns Price and Quantity are numeric data types as shown in the below query. I have also inserted a few rows in this table for our use cases.
You can also create this test table and insert some dummy data using the below query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
USE [TESTDB] GO CREATE TABLE [dbo].[Sales]( [ProductName] [nchar](10) NULL, [Price] [money] NULL, [Quantity] [int] NULL, [InvoiceMonth] [nchar](10) NULL, [City] [nchar](10) NULL ) ON [PRIMARY] GO USE [TESTDB] GO INSERT INTO [dbo].[Sales] VALUES ('A',10,2,'July','Delhi'), ('B',15,3,'June','Mumbai'), ('A',5,1,'May','Delhi'), ('C',50,1,'July','Delhi'), ('A',10,2,'May','Mumbai'), ('B',20,4,'June','Delhi'), ('A',10,2,'July','Mumbai') |
Below is the table in which I have created and inserted a few rows using the above query in the test database TESTDB. Now, I will show you all the use cases on this table and the data that is shown in the below image.
Use cases of the SQL SUM statement
Here, I will explain below the use cases of the SQL SUM() function in their respective section.
- Use SQL SUM() function with ALL and DISTINCT arguments
- Use SQL SUM() function with GROUP BY, HAVING, and ORDER BY statements
- Use SQL SUM() function with other aggregate functions like MIN, MAX & AVG
- Use SQL SUM() function with Expressions
- Use SQL SUM() function with the NULL value present in a numeric column
Use case with ALL & DISTINCT
Let’s start with the first use case in which we will understand two arguments ALL and DISTINCT and their output. I have mentioned above that argument ALL is the default argument and will be used by SQL Server in case you will not specify any of these arguments. I will prove this point with the help of the below example in which I will run a query with ALL and DISTINCT arguments along with a statement without mentioning any of these arguments and then we can compare their output.
I want to add all values stored in column Price of table Sales. I have used ALL arguments in the first statement, the DISTINCT argument in the second statement, and have not mentioned any argument in the third statement.
|
1 2 3 4 5 6 |
SELECT SUM( ALL Price) AS [Total Sales - ALL], SUM( DISTINCT Price) AS [Total Sales - DISTINCT], SUM(Price) AS [Total Sales w/o argument] FROM Sales |
We can see that the first and third statements returned the same values whereas the second statement returned different values. This is because the first and third statements that are using argument ALL have added all values whereas the second statement where argument DISTINCT has been specified has only added unique values and skipped duplicate values.
This way you can find out the total sum or sum of unique values stored in a numeric column.
Use case with GROUP BY, HAVING, and ORDER BY statement
Suppose you got a requirement to return a total number of units sold along with their total values, then we can use the SQL SUM function on two columns. One is storing the values of the products and another one is storing the number of units being sold.
I have used this function on these columns and get the output by using the below query.
|
1 2 3 4 5 |
SELECT SUM(Price) AS [Total Sales], SUM(Quantity) AS [Total units sold] FROM Sales |
We can see there are a total of 15 units that have been sold for $ 120.
Next, suppose there is a modification in this requirement and now, you want to list out month-wise sales data in which we must tell the total values sold each month. We will use the GROUP BY statement to group all month-wise data and display the result for our requirement.
This table Sales is storing month-related information in the InvoiceMonth column so we will specify this column with GROUP BY statement as I have used in the below query to club all monthly data together and display its result. I have also used the system function SUM() to add all values which were invoiced that month.
|
1 2 3 4 5 |
SELECT InvoiceMonth, SUM(Price) AS [Total Sales] FROM Sales GROUP BY InvoiceMonth |
We can see the output of the above query in month-wise sales is showing. The SQL SUM() function has added all the values together for each respective month which is grouped by the GROUP BY statement.
Similarly, we can also get another aspect of our sales data by specifying the ProductName column in the GROUP BY statement to return product-wise sales data. You can get which product is generating most of the revenue using this query.
|
1 2 3 4 5 |
SELECT ProductName, SUM(Price) AS [Total Sales] FROM Sales GROUP BY ProductName |
The output of the above query is showing each product and its respective sales data.
If you want to get the city-wise sales data then you can also get that by specifying the related column with the GROUP BY statement along with applying the SQL SUM function on the Price column as I have done in the below query.
|
1 2 3 4 5 |
SELECT City, SUM(Price) AS [Total Sales] FROM Sales GROUP BY City |
We can also use the SQL SUM function with another SQL statement HAVING together with the GROUP BY statement to filter our output further by a condition specified with the HAVING statement. I am taking a reference query from the above example where I have returned product-wise sales data and added a HAVING statement in its GROUP BY clause with a condition on column Quantity for more than 5 sales. It means the query will return the same product-wise sales data but only for those products which have been sold 5 or more units.
|
1 2 3 4 5 |
SELECT ProductName, SUM(Price) AS [Total Sales] FROM Sales GROUP BY ProductName HAVING SUM(Quantity)>5 |
You can compare this output with the above image where product-wise sales data is given. You can see product C is not shown in the below image, which means that product C has sold less than 5 units and products A and B sold more than 5 units.
Next, I will add one more SQL statement ORDER BY to display the result in a specified order. I have taken another example from above where month-wise sales have been returned then added a HAVING clause on column quantity to display all results based on its condition and then finally I specify ORDER BY statement to list out its result in a specific order.
|
1 2 3 4 5 6 |
SELECT InvoiceMonth, SUM(Price) AS [Total Sales] FROM Sales GROUP BY InvoiceMonth HAVING SUM(Quantity)>1 ORDER BY InvoiceMonth |
You can see the output of the above query in the below image.
Use case with other system functions like MIN, MAX, AVG
The SQL SUM() function can also be used along with other aggregate functions like MAX, AVG, & MIN. The details of these system functions are given in the below pointers.
- MAX – This function will return the highest or maximum value from the specified column or expression.
- AVG – This will return the average value of a specified column or expression.
- MIN – This function will return the minimum values of a specified column or expression.
Here, I have used all these system functions in the below query to return their respective values from our source table Sales. It’s very easy to get these details using these system functions.
|
1 2 3 4 5 6 7 8 |
SELECT MIN (Price) AS [MinimumInvoice], MAX(Price) AS [MaximumInvoice], AVG(Price) AS [Average Sales], SUM(Price) AS [Total Sales] FROM Sales GO |
We can see the output of the above query. You can validate its output by looking at the table output given in the source table section. Here, the query has returned minimum invoice values, maximum invoice values, and average invoice values along with total invoice values saved in the specified column Price.
If you have a requirement to get similar values for each month like monthly minimum, maximum, monthly average, and total month invoice data then we can get that by adding another SQL statement GROUP BY to list all the above values as it is showing in the below image.
Below is the example where I have used other aggregate functions to get the lowest, highest, average, and total invoice cost for each month.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT InvoiceMonth, MIN (Price) AS [MinimumInvoice], MAX(Price) AS [MaximumInvoice], AVG(Price) AS [Average Sales], SUM(Price) AS [Total Sales] FROM Sales GROUP BY InvoiceMonth GO |
Here is its output where you can get similar data for each month.
Use case with Expressions
The SQL SUM() function can also be used with arithmetic expressions. Suppose we have two columns Price and Quantity. The price column is storing the per unit cost of each product and the Quantity column is storing the total units sold. If you want to return product-wise total sales value, then you need to multiply both columns Price and Quantity, and then add all product-wise sales values. This function helps us to get this kind of calculation easily.
I multiplied both columns and then used the GROUP BY statement to club all product-wise data and then add them to display the total monthly sales.
|
1 2 3 4 5 6 7 |
SELECT ProductName, SUM(Price*Quantity) AS [Total Sales] FROM Sales GROUP BY ProductName GO |
Here is the output of such a calculation.
Use case with having a NULL value in column
I have stated above that the SQL SUM() function skips NULL values from any numeric column during its calculation. I will prove this statement in this section. Let’s first insert a few additional rows with NULL values and then list out all values from the table to verify our next output.
The first statement is displaying all values from table Sales and the second query adds all values stored in the column Price and Quantity to demonstrate it is skipping the NULL values.
|
1 2 3 4 5 6 7 |
SELECT * FROM Sales GO SELECT SUM(Price) AS [Total Sales], SUM(Quantity) AS [Total units] FROM Sales |
We can see the NULL values in both columns returned by the first query and the second query has returned the sum of the values stored in columns Price and Quantity. Here we can see NULL values have not been considered while returning this output.
Conclusion
I have explained the SQL SUM() function in this article. This function is very useful in getting a sum of all the values stored in a numeric column. We can use this system function with other SQL clauses like GROUP BY, HAVING, and ORDER BY. You must try this system function to get such mathematical calculations easily. Please let us know your feedback in the comment section.
He loves to talk and write about database technologies. He has lead and delivered many projects from designing to deployments on Migrations to the cloud, heterogeneous migrations, Database consolidations, upgrades, heterogeneous replication, HA / DR solutions, automation, and major performance tuning projects.
You can also find him on LinkedIn
View all posts by Manvendra Singh
- Configure backup storage redundancy for Azure Cosmos DB account - May 15, 2024
- Difference between SQL SELECT UNIQUE and SELECT DISTINCT - January 16, 2024
- How to do a group by clause - April 12, 2023