Sum function is used to perform summation of all the values in the expression. It can be used with numeric type columns only. Null values are ignored while performing summation of an expression.

Syntax of SUM function :

SUM ( [ ALL | DISTINCT ] expression )

ALL is the default. It means all values of the column or expression.

DISTINCT performs summation of unique values only.

Expression is avalid expression in sql sever of type numeric except bit data type. It can be made up of constant, column or function.

Examples of SUM function :

Example 1: Using sum function in SELECT clause 

SELECT SUM(UnitPrice) AS Total

FROM   Products

Output

Total

2196.71

Above example displays total of unitprice using sum function.

Example 2 : Using sum function with DISTINCT

SELECT SUM(DISTINCT UnitPrice) AS Total

FROM   Products

Output

Total

1921.81

Above example performs summation of unit price by taking unique values of product using SUM function with DISTINCT clause.

Example 3 : Using sum function in SELECT clause with GROUP BY clause 

SELECT ProductName,SUM(Quantity) AS ‘Product Quantity Sold’

FROM   Invoices

GROUP  BY ProductName 

Output

ProductName                           Product Quantity Sold

Gula Malacca                                       601

Longlife Tofu                                       297

Ravioli Angelo                                     434

Mishi Kobe Niku                                    95

Queso Manchego La Pastora               344

Above example displays productwise sum of quantity sold.