Calculating Sums with SUM

Calculating totals using the SUM() aggregate function is a fundamental way to summarize data. Often, you'll need to know the total value across many rows, such as total sales revenue, total quantity of items sold, or total distance traveled. The SUM() function is designed specifically for this purpose.

What Does SUM() Do?

The SUM() function calculates the sum of all values in a specified numeric column. It operates on a set of rows (either the entire table or a subset defined by a WHERE clause) and returns a single value representing the total.

It's important to remember that SUM() only works with numeric data types like INTEGER, DECIMAL, FLOAT, NUMERIC, etc. Attempting to use SUM() on text (VARCHAR) or date (DATE) columns will result in an error from the database.

Basic Syntax

The basic structure for using SUM() is straightforward:

SELECT SUM(numeric_column)
FROM table_name;

Here, numeric_column is the name of the column whose values you want to add together, and table_name is the table containing that column.

Calculating Total Quantity

Imagine we have an OrderItems table that records each item included in customer orders:

OrderItems Table:

OrderItemID	OrderID	ProductID	Quantity	UnitPrice
1	101	P001	2	10.00
2	101	P005	1	25.50
3	102	P002	5	5.00
4	103	P001	3	10.00
5	103	P008	1	150.00
6	104	P005	4	25.50

To find the total number of items sold across all orders, we can sum the Quantity column:

SELECT SUM(Quantity)
FROM OrderItems;

This query processes the Quantity column for every row in the OrderItems table and returns a single result:

SUM(Quantity)
16

The result, 16, is the sum of 2 + 1 + 5 + 3 + 1 + 4.

Calculating Total Revenue

We can also perform calculations within the SUM() function. For instance, to calculate the total revenue from all order items, we need to multiply the Quantity by the UnitPrice for each item and then sum those results.

SELECT SUM(Quantity * UnitPrice)
FROM OrderItems;

The database first calculates Quantity * UnitPrice for each row:

• Row 1: $2 * 10.00 = 20.00$
• Row 2: $1 * 25.50 = 25.50$
• Row 3: $5 * 5.00 = 25.00$
• Row 4: $3 * 10.00 = 30.00$
• Row 5: $1 * 150.00 = 150.00$
• Row 6: $4 * 25.50 = 102.00$

Then, it sums these values: $20.00 + 25.50 + 25.00 + 30.00 + 150.00 + 102.00 = 352.50$.

The query result would be:

SUM(Quantity * UnitPrice)
352.50

Using Aliases for Clarity

The default column name for the result (like SUM(Quantity) or SUM(Quantity * UnitPrice)) isn't very descriptive. As we saw with selecting columns, we can use the AS keyword to assign a more meaningful alias to the result of our SUM() calculation.

SELECT
  SUM(Quantity) AS TotalItemsSold,
  SUM(Quantity * UnitPrice) AS TotalRevenue
FROM OrderItems;

This query calculates both sums in one go and presents them with clear column names:

TotalItemsSold	TotalRevenue
16	352.50

Summing Filtered Data with WHERE

Just like COUNT(), the SUM() function can be combined with a WHERE clause to calculate totals for only a specific subset of your data. For example, let's calculate the total revenue generated only from orders involving ProductID 'P001'.

SELECT SUM(Quantity * UnitPrice) AS RevenueFromP001
FROM OrderItems
WHERE ProductID = 'P001';

The database first filters the OrderItems table to include only rows where ProductID is 'P001' (rows 1 and 4 in our example). Then, it applies the SUM() function to the Quantity * UnitPrice calculation for just those filtered rows:

• Row 1: $2 * 10.00 = 20.00$
• Row 4: $3 * 10.00 = 30.00$
• Sum: $20.00 + 30.00 = 50.00$

Result:

RevenueFromP001
50.00

How SUM() Handles NULL Values

What happens if the column you're summing contains NULL values? The SUM() function simply ignores them. It calculates the sum using only the non-NULL values in the column. If all values in the column for the selected rows are NULL, SUM() returns NULL. This behavior is generally what you want, as NULL typically represents unknown or missing data, which shouldn't be treated as zero in a summation.

For example, if UnitPrice for OrderItemID 5 was NULL, the TotalRevenue calculation would exclude that row's contribution entirely.

The SUM() function is a powerful tool for calculating totals within your datasets, whether across an entire table or specific subsets. Next, we'll look at how to calculate averages using the AVG() function.