SUM function in SQL Server

This article 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 :

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

Note :

1. 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 example

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

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

 SELECT SUM(Sobai) AS "Tong so bai" 
 FROM Quantrimang 
 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 Quantrimang 
 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 following example 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 Quantrimang 
 GROUP BY Chuyenmuclon; 
 
 Result: 
 Chuyenmuclon Tong so bai 
 Laptrinh 324 
 Mang xa hoi 317 
 Trinh duyet web 94 

Previous article: SIGN function in SQL Server

Next lesson: CURRENT_TIMESTAMP command in SQL Server