SQL Server SUM() Function

Summary: in this tutorial, you will learn how to use SQL Server SUM() function to calculate the sum of values.

Introduction to SQL Server SUM() function #

The SQL Server SUM() function is an aggregate function that calculates the sum of all or distinct values in an expression.

Here’s the syntax of the SUM() function:

SUM([ALL | DISTINCT ] expression)Copy

In this syntax:

• ALL instructs the SUM() function to return the sum of all values including duplicates. ALL is used by default.
• DISTINCT instructs the SUM() 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 to demonstrate the difference between ALL and DISTINCT options:

CREATE TABLE t(
    val INT
);

INSERT INTO t(val)
VALUES(1),(2),(3),(3),(4),(NULL),(5);

SELECT
    val
FROM
    t;
Copy

Output:

val
-----------
1
2
3
3
4
NULL
5

(7 rows affected)Copy

The following statement uses the SUM() function to calculate the total of all values in the val column:

SELECT
    SUM(val) total
FROM
    t;Copy

Output:

total
-----------
18
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row affected)Copy

However, when you use the DISTINCT modifier, the SUM() function returns the sum of unique values in the val column:

SELECT
    SUM(DISTINCT val) total
FROM
    t;Copy

Output:

total
-----------
15
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row affected)Copy

SQL Server SUM() function examples #

Let’s take some practical examples of using the SUM() function. We’ll use the tables from the sample database for the demonstration.

1) Basic SQL Server SUM() function example #

The following statement uses the SUM() function to calculate the total stocks of all products in all stores:

SELECT 
    SUM(quantity) total_stocks
FROM 
    production.stocks;Copy

Output:

total_stocks
------------
13511

(1 row affected)Copy

2) Using the SUM() function with GROUP BY example #

The following statement uses the SUM() function with the GROUP BY clause to find total stocks by store id:

SELECT
    store_id,
    SUM(quantity) store_stocks
FROM
    production.stocks
GROUP BY
    store_id;Copy

Here is the output:

store_id | store_stocks
---------+------------
1        | 4532
2        | 4359
3        | 4620
(3 rows)Copy

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;Copy

Output:

store_name       | store_stocks
-----------------+------------
Baldwin Bikes    | 4359
Rowlett Bikes    | 4620
Santa Cruz Bikes | 4532
(3 rows)Copy

3) Using the SUM() function with HAVING clause example #

The following statement uses the SUM() function in the HAVING clause to find stocks for each product and return 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;Copy

Output:

product_name                                          | total_stocks
------------------------------------------------------+--------------
Electra Townie Original 7D - 2017                     | 125
Electra Townie Balloon 8D EQ Ladies' - 2016/2017/2018 | 121
Electra Townie Go! 8i - 2017/2018                     | 120
Electra Townie Commute 8D - 2018                      | 119
Sun Bicycles Cruz 7 - 2017                            | 115
Surly Straggler - 2018                                | 109
Sun Bicycles Cruz 3 - 2017                            | 109
Electra Townie Original 21D - 2018                    | 109
Electra Girl's Hawaii 1 16" - 2017                    | 107
(9 rows)\Copy

4) Using the 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;
Copy

Output:

order_id | net_value
---------+------------
1541     | 29147.0264
937      | 27050.7182
1506     | 25574.9555
1482     | 25365.4344
...Copy

Summary #

• Use the SUM() function to calculate the sum of values.
• Use the DISTINCT option to calculate the sum of distinct values.
• Use the ALL option to calculate the sum of all values including duplicates.

Was this tutorial helpful?

YesNo