Manvendra Singh
Use SUM with other system functions and GROUP BY statement

Understanding the SQL SUM() function and its use cases

December 13, 2022 by

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.

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.

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.

Source Table Sales

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.

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 of SQL SUM function

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.

We can see there are a total of 15 units that have been sold for $ 120.

Use case of SUM function

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.

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.

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.

The output of the above query is showing each product and its respective sales data.

SUM function with GROUP BY statement

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.

SUM function with GROUP BY statement

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.

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.

SUM function with GROUP BY and HAVING statement

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.

You can see the output of the above query in the below image.

SUM function with GROUP BY statement HAVING and Order BY

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.

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.

Use SUM with other system functions

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.

Here is its output where you can get similar data for each month.

Use SUM with other system functions and GROUP BY statement

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.

Here is the output of such a calculation.

Use SUM with Expressions

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.

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.

Use case with NULL values

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.

Manvendra Singh
Functions, T-SQL

About Manvendra Singh

Manvendra is a database enthusiast, currently working as a Senior Architect at one of the top MNC. 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

3,376 Views