SQL Server SUM() Function By Practical Examples

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

)

SQL Server SUM function ALL vs DISTINCTSQL Server SUM function ALL vs DISTINCT

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:

SQL Server SUM function with GROUP BY example

SQL Server SUM function with GROUP BY example

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:

SQL Server SUM function with GROUP BY and JOIN exampleSQL Server SUM function with GROUP BY and JOIN example

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:

SQL Server SUM function with GROUP BY and HAVING exampleSQL Server SUM function with GROUP BY and HAVING example

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:

SQL Server SUM function with expression exampleSQL Server SUM function with expression example

In this tutorial, you have learned how to use the SQL Server SUM() function to calculate the sum of values.

Alternate Text Gọi ngay