SQL SUM() Function

The SUM() function in SQL calculates the sum (total) of numeric values in a column. It's a fundamental aggregate function used for summarizing numerical data.

SUM(): Definition and Usage

SUM() is incredibly useful for getting totals from your data. It automatically ignores any NULL values, providing a clean sum of the non-null numeric values in the specified column.

Basic Syntax

SELECT SUM(column_name)
FROM table_name
WHERE condition;
      

Example Database: OrderDetails Table

The following examples use this sample 'OrderDetails' table:

Examples

Calculating the Total Quantity

This example calculates the sum of the 'Quantity' column in the 'OrderDetails' table.

SELECT SUM(Quantity) FROM OrderDetails;
      

76
      

Calculating the Total Quantity with a Condition

This calculates the sum of 'Quantity' only for 'ProductID' 11.

SELECT SUM(Quantity) FROM OrderDetails WHERE ProductId = 11;
      

12
      

Using an Alias

This gives the sum a name ('total').

SELECT SUM(Quantity) AS total FROM OrderDetails;
      

total
-------
76
      

Using SUM() with GROUP BY

This groups the results by 'OrderID' and sums the quantities for each order.

SELECT OrderID, SUM(Quantity) AS [Total Quantity]
FROM OrderDetails
GROUP BY OrderID;
      

OrderID | Total Quantity
------------------------
10248   | 27
10249   | 49
      

SUM() with an Expression

This example multiplies each quantity by 10 (assuming a cost of 10 per item) to get the total earnings.

SELECT SUM(Quantity * 10) FROM OrderDetails;
      

760
      

SUM() with a JOIN

This joins 'OrderDetails' with 'Products' to calculate the total amount based on actual product prices (assuming a 'Products' table exists with a 'Price' column).

SELECT SUM(Price * Quantity)
FROM OrderDetails
LEFT JOIN Products ON OrderDetails.ProductID = Products.ProductID;
      

(The total value of all orders based on product prices)