Summary: in this tutorial, you will learn how to use SQL Server SUM()
function to calculate the sum of values.
The SQL Server SUM()
function is an aggregate function that calculates the sum of all or distinct values in an expression.
The syntax of the SUM()
function is as follows:
SUM([ALL | DISTINCT ] expression)
Code language: SQL (Structured Query Language) (sql)
In this syntax:
ALL
instructs theSUM()
function to return the sum of all values including duplicates.ALL
is used by default.DISTINCT
instructs theSUM()
function to calculate the sum of the only distinct values.-
expression
is any valid expression that returns an exact or approximate numeric value. Note that aggregate functions or subqueries are not accepted in the expression.
The SUM()
function ignores NULL
values.
ALL vs. DISTINCT
Let’s create a new table for demonstration the difference between ALL
and DISTINCT
:
CREATE TABLE t(
val INT
);
INSERT INTO t(val)
VALUES(1),(2),(3),(3),(4),(NULL),(5);
SELECT
val
FROM
t;
Code language: SQL (Structured Query Language) (sql)
The following statement returns the sum of all values in the val
column:
SELECT
SUM(val) total
FROM
t;
Code language: SQL (Structured Query Language) (sql)
The output is:
total
-----------
18
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row affected)
Code language: PHP (php)
However, when we use the DISTINCT
modifier, the SUM()
function returns the sum of only unique values in the val
column:
SELECT
SUM(DISTINCT val) total
FROM
t;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
total
-----------
15
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row affected)
Code language: PHP (php)
SQL Server SUM()
function examples
Let’s take some practical examples of using the SUM()
function.
A) Simple SQL Server SUM()
function example
The following statement returns the total stocks of all products in all stores:
SELECT
SUM(quantity) total_stocks
FROM
production.stocks;
Code language: SQL (Structured Query Language) (sql)
The following shows the output:
total_stocks
------------
13511
(1 row affected)
B) SQL Server SUM()
function with GROUP BY
example
The following statement finds total stocks by store id:
SELECT
store_id,
SUM(quantity) store_stocks
FROM
production.stocks
GROUP BY
store_id;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
In this example:
- First, the
GROUP BY
clause divided the stocks by store id into groups. - Second, the
SUM()
function is applied to each group to calculate the total stocks for each.
If you want to display the store name instead of store id, you can use the following statement:
SELECT
store_name,
SUM(quantity) store_stocks
FROM
production.stocks w
INNER JOIN sales.stores s
ON s.store_id = w.store_id
GROUP BY
store_name;
Code language: SQL (Structured Query Language) (sql)
The following picture shows the output:
C) SQL Server SUM()
function with HAVING
clause example
The following statement finds stocks for each product and returns only products whose stocks are greater than 100:
SELECT
product_name,
SUM(quantity) total_stocks
FROM
production.stocks s
INNER JOIN production.products p
ON p.product_id = s.product_id
GROUP BY
product_name
HAVING
SUM(quantity) > 100
ORDER BY
total_stocks DESC;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
D) SQL Server SUM()
function with expression example
The following example uses an expression in the SUM()
function to calculate the net value for each sales order:
SELECT
order_id,
SUM(
quantity * list_price * (1 - discount)
) net_value
FROM
sales.order_items
GROUP BY
order_id
ORDER BY
net_value DESC;
Code language: SQL (Structured Query Language) (sql)
The output is as follows:
In this tutorial, you have learned how to use the SQL Server SUM()
function to calculate the sum of values.