SUM to Add Values

1. Getting Started

SUM is an aggregate function that returns the sum of all of the values in a column given a filter. Similar to the COUNT function, the SUM function ignores NULL values in a column. The SUM function is used on numeric columns. For example, if we would like to find the sum of the total amount for all the invoices, then we run the following:

SELECT SUM(total)
FROM invoice;

This would SUM the values in the total column that are not NULL, which would give the result:

We may also want to SUM based on certain criteria to be restricted by using the WHERE clause. For example, we could find the SUM of the total amount for all invoices in the USA:

SELECT SUM(total)
FROM invoice
WHERE billing_country = 'USA';

Anything that we can filter with the WHERE clause, we can then aggregate to find the SUM for.

Similar to the COUNT function, we can also use the DISTINCT option where it’ll only calculate the SUM of the DISTINCT values. For example, if we had a total of 1, 2, 2, and 4. If we used the regular SUM function, the function would return 9. However, if we use DISTINCT, the query will return 7 as 2 was repeated.

2. Special Cases

If we tried to use the SUM on a non-numeric column, we would result in an error:

SELECT SUM(billing_country)
FROM invoice;

If the query returns only NULL values or if no rows are returned, the SUM returns NULL rather than 0. For example, if we’re looking for the SUM of all of the invoices that have the invoice_id < 1 (which does not exist):

SELECT SUM(total)
FROM invoice
WHERE invoice_id < 1;

The result will look like this:

If we wanted to return a 0 instead of NULL in this case, we would have to use the COALESCE function. This function returns the second argument if the first argument is NULL. For example:

SELECT COALESCE(SUM(total),0)
FROM invoice
WHERE invoice_id < 1;

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.