SUM Function in SQL Server: What You Need to Know

SUM Function in SQL Server is easier to understand when the core ideas are paired with practical examples. The sections below explain the topic clearly, highlight useful steps, and point out details that can prevent common errors.

This guide will show you in detail how to use the SUM () numeric processing function in SQL Server with specific syntax and examples to better visualize and capture functions.

Describe

The SQL Server SUM function returns the total value of a column, a data set, or an expression.

Syntax

To use the SUM function in SQL Server, we use the following syntax:

 SELECT SUM(cot) 
 FROM bang 
 [WHERE dieukien];

Parameters :

cot: column or calculated value, the expression you want to sum
state: the table used to retrieve the record. Must have at least 1 table in the FROM clause.
dieukien: optional. Conditions that the record must meet to be selected.

Note :

The SUM function can be used in later versions of SQL Server: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005.

For instance,

Take a look and explore some examples of SUM functions in SQL Server.

Suppose, we have the following data table:

SUM Function in SQL Server example image 1

Example 1: Calculate the total number of articles of website Tipsmake

 SELECT SUM(Sobai) AS "Tong so bai" 
 FROM Tipsmake 
 WHERE Sobai > 100; 
 
 Result: Tong so bai 641

In this example we need to output the value of the total number of articles of the website, so the result is calculated by adding all values in the Sobai column with different data and greater than 100 together.

Example 2: Use DISTINCT

 SELECT SUM(DISTINCT Sobai) AS "Tong so bai" 
 FROM Tipsmake 
 WHERE Sobai > 100; 
 
 Result: Tong so bai 540

This example uses the keyword DISTINCT, so the repeated values are counted only once. In the given data table, the value '101' appears twice, but only once, so the total number of calculated lines will be 4, the result is calculated as follows:

 152 + 101 + 122 + 165 = 540

Example 3: Calculate the total value by the column specified by the selected row

To calculate the total value by the specified column of the selected rows, we use the GROUP BY clause.

the example below calculates the total value of all records related to a large section and you will do the following:

 SELECT Chuyenmuclon, SUM(Sobai) AS "Tong so bai" 
 FROM Tipsmake 
 GROUP BY Chuyenmuclon; 
 
 Result: 
 Chuyenmuclon Tong so bai 
 Laptrinh 324 
 Mang xa hoi 317 
 Trinh duyet web 94

Next lesson: CURRENT_TIMESTAMP command in SQL Server