SQL SUM Function

The SQL SUM function returns the total value within a table or group.

In its simplest use, SUM() totals a column for all results and returns a single value.

In this example the query returns the total orders for all SalesOrderDetail records

SELECT SUM(LineTotal) AS OrderTotal
FROM Sales.SalesOrderDetail

You can also filter your results prior to totaling. Here the SUM() is restricted to two SalesOrders:

SELECT SUM(LineTotal) AS OrderTotal
FROM Sales.SalesOrderDetail
WHERE SalesOrderID in (43659, 43662)

Of course it’s more fun to use SUM() when working with GROUPS! Rather than sum two sales orders, let’s return a summary of sales, by SalesOrder. To do so, we’ll use SQL SUM in conjunction with GROUP BY.

SELECT SalesOrderID,
       SUM(LineTotal) AS OrderTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY SalesOrderID

Now that you’ve seen SUM() in actions. Let’s check out some other places you can use it!

For instance did you know you can sort by SQL SUM. Here’s the same query now ordering result by the OrderTotal:

SELECT SalesOrderID,
       SUM(LineTotal) AS OrderTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY SUM(LineTotal)

What’s really cool about this is the data is grouped by SalesOrderID, but sorted by the total. Nifty huh?

Additional SQL SUM Resources  

To learn more, check out these useful resources: 

• SQL GROUP BY Guide
• How to Calculate a Running Total 
• SQL AVG Function
• The New SQL Server Approximate Count Distinct Function