SUM

When working with large amounts of data, you often need to find the sum of a particular column to answer a variety of common analytical questions. In SQL, we use the SUM operator to achieve this. In this lesson, we'll look at how to use SUM in basic and advanced use cases to find sums across tables and groups of rows.

Basic syntax

The syntax for finding the sum of a column is as follows:

SQL
SELECT SUM(column_name)
FROM table_name;

Consider the following example, which represents purchase orders from a store. We have a table named orders with the following structure:

Let’s say we want to find the total quantity of units sold. With the SUM operator, we can calculate it easily:

SQL
SELECT SUM(quantity) as total_quantity
FROM orders;

Running this query would output a single result cell like this:

Calculating sums across multiple columns

In many cases, you might want to calculate the sum of a value that isn’t explicitly represented in the columns of the table. For example, in the table above we don’t have a column for the total revenue of each order, but we do have the quantity and price per item. To calculate the total dollar amount in this case, you could multiply the quantity and the price for each row and then sum up the result:

SQL
SELECT SUM(quantity * price) as total_revenue
FROM purchase_orders;

This query will return the total revenue for all orders, by summing the quantity and price for each row, as illustrated by the following:

SUM = (quantity1 * price1) + (quantity2 * price2) + …

Note that you usually only need the SUM method if you want to sum the value of columns across`rows. If you only need to calculate the sum within rows for a given set of columns, you can use normal arithmetic on those rows instead.

Grouping sums with GROUP BY

In other cases, we may want to find the sums grouped by another column within the table. Continuing the example above, let's say we want to see the revenue by country. To do this, we simply add GROUP BY country to the query from before:

SQL
SELECT country, SUM(quantity * price) AS total_revenue
FROM orders
GROUP BY country
ORDER BY total_revenue DESC;

This query will return the total revenue for each country, sorted in descending order.

Try it yourself

Let's try a practice problem using an orders table similar to the above examples. Can you find the total revenue per customer?