SUM() Function in SQL Server

Last Updated : 17 Sep, 2024

The SUM() function in SQL Server is an essential aggregate function used to calculate the total sum of values in a numeric column. It aggregates data by summing up all values in the specified column for the rows that match the criteria of the query.

In this article, We will learn about SUM() Function in SQL Server by understanding various examples and so on.

SUM() Function in SQL Server

The SUM() function in SQL Server is an aggregate function used to calculate the total sum of a numeric columns values.
It adds up all the values in a given column for the rows selected by the query.

Features of SUM() Function in SQL Server

This function is used to compute the sum of the specified group of values.
This function comes under Numeric Functions.
This function accepts only one parameter namely expression.
This function ignores the null value.

Syntax:

SUM(expression)

Parameter:

This method accepts one parameter.

expression: A specified expression which can either be a field or a given formula.

Returns:

It returns the sum of the specified group of values.

Examples of SUM() Function in SQL Server

Example 1:

Using SUM() function and getting the output.

CREATE TABLE product
(  
user_id int IDENTITY(100, 2) NOT NULL,    
product_1 VARCHAR(10),
product_2 VARCHAR(10),
price int  
);
INSERT product(product_1, price)  
VALUES ('rice', 400);

INSERT product(product_2, price)  
VALUES ('grains', 600);

SELECT SUM(price) FROM product;

Output:

Example 2:

Using SUM() function and finding the sum of all the stated float values.

CREATE TABLE floats
(  
user_id int IDENTITY(100, 2) NOT NULL,
float_val float
);
INSERT floats(float_val)  
VALUES (3.6);

INSERT floats(float_val)  
VALUES (2.1);

INSERT floats(float_val)  
VALUES (6.3);

INSERT floats(float_val)  
VALUES (9.0);

INSERT floats(float_val)  
VALUES (7.0);

SELECT SUM(float_val) FROM floats;

Output:

Example 3:

Using SUM() function and getting the output where MRP is less than the sum of all the MRP’s.

CREATE TABLE package
(  
user_id int IDENTITY(100, 4) NOT NULL,  
item VARCHAR(10),
mrp int  
);
INSERT package(item, mrp)  
VALUES ('book1', 3);

INSERT package(item, mrp)  
VALUES ('book2', 350);

INSERT package(item, mrp)  
VALUES ('book3', 400);

SELECT * FROM package
WHERE mrp < (SELECT SUM(mrp) FROM package);

Output:

user_id	item	mrp
100	book1	3
104	book2	350
108	book3	400

Example 4:

Using SUM() function and getting the sum of all the (MRP-sales price) values.

CREATE TABLE package
(  
user_id int IDENTITY(100, 4) NOT NULL,  
item VARCHAR(10),
mrp int,
sp int
);
INSERT package(item, mrp, sp)  
VALUES ('book1', 250, 240);

INSERT package(item, mrp, sp)  
VALUES ('book2', 350, 320);

INSERT package(item, mrp, sp)  
VALUES ('book3', 400, 350);

SELECT SUM(mrp-sp) FROM package;

Output:

Conclusion

The SUM() function is an essential part of SQL Server, enabling developers to quickly and easily compute the total of numeric columns. Whether it’s used for financial calculations, data analysis, or other numeric operations, SUM() plays a vital role in data aggregation.