SQL Server SUM() Function

The SUM() function is an aggregate function in SQL that calculates the sum of specified column values. In SQL Server, SUM() can be used on columns of integer, decimal, float, and currency data types.

Syntax

The syntax of the SUM() function is as follows:

SUM(column_name)

Where column_name is the name of the column to sum.

Use Cases

The SUM() function is commonly used to calculate the total of a set of data. For example, it can be used to calculate the total salary of all employees in a department.

Examples

Here are two examples of using the SUM() function:

Example 1

Suppose there is an orders table that contains the following fields:

order_id	customer_id	order_date	order_amount
1	101	2022-01-01	100.00
2	102	2022-01-02	200.00
3	101	2022-01-03	150.00
4	103	2022-01-04	75.00
5	102	2022-01-05	225.00

To calculate the total amount of orders, you can use the following SQL statement:

SELECT SUM(order_amount) as total_amount
FROM orders;

The result of the query is:

total_amount
750.00

Example 2

Suppose there is a sales table that contains the following fields:

sale_id	product_name	sale_date	sale_quantity	sale_price
1	Product A	2022-01-01	10	50.00
2	Product B	2022-01-02	20	75.00
3	Product C	2022-01-03	5	100.00
4	Product A	2022-01-04	15	50.00
5	Product B	2022-01-05	30	75.00

To calculate the total sales for each product, you can use the following SQL statement:

SELECT
    product_name,
    SUM(sale_quantity * sale_price) as total_sales
FROM sales
GROUP BY product_name;