SQL SUM Function

SUM returns the sum of a number of value.

This function ignores NULL values.

Example

#

This example returns the sum of all orders.

SELECT SUM(TotalAmount) AS Total
  FROM [Order]
Result:  1 record
Total
1354458.59

Syntax

Syntax of the SUM function.

SUM(value)

value -- a value, numeric column, or subquery.

More Examples

SUM with GROUP BY

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List the total sales by month for the year 2013.
SELECT MONTH(OrderDate) AS Month
       SUM(TotalAmount) AS 'Total Sales'
  FROM [Order]
 WHERE YEAR(OrderDate) = 2013
 GROUP BY MONTH(OrderDate)
Result:  12 records
Month Total Sales
1 66692.80
2 41207.20
3 39979.90
4 55699.39
5 56823.70

SUM with WHERE

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
ORDERITEM
Id
OrderId
ProductId
UnitPrice
Quantity
Problem: Find the total sales for Tofu in the year 2014. The productId for Tofu is 14.
SELECT SUM(UnitPrice * Quantity) AS 'Total Sales'
  FROM [Order] O
  JOIN OrderItem I ON O.Id = I.OrderId
 WHERE I.ProductId = 14 AND YEAR(OrderDate) = 2014
Result:  1 record
Total Sales
488.25

You may also like



Guides


vsn 3.1