How to Sum in SQL

One of the most basic things you need to know how to do when beginning to writing SQL queries is how to sum in SQL. This is the process of taking a result set and adding up the values of all the records within a certain column of each row.

A common task might be to add up the total cost of all line items on a customer order. We’ll use this example for this article. We will assume that you have a basic table that includes an Order ID, a product name, a cost, a sale price, and the number of units. This will allow us to perform a number of different calculations. We’ll be able to calculate the total cost, the total sale price, and the net profit for each order.

Table of Contents

Setting Up Our Test Data for SUM Queries

Here is the table that we will use:

CREATE TABLE order_details (
    order_id varchar(25),
    product_name varchar(50),
    product_cost decimal(12, 2),
    sale_price decimal(12, 2),
    order_units int
);

And here are the insert scripts that we’ll run to populate our data:

INSERT INTO order_details (order_id, product_name, product_cost, sale_price, order_units) VALUES ('ORD0001234', 'Titanium Wedding Men''s Band', 9.40, 34.95, 1);
INSERT INTO order_details (order_id, product_name, product_cost, sale_price, order_units) VALUES ('ORD0001234', 'Platinum & Diamond Women''s Wedding Band', 13.10, 69.95, 1);
INSERT INTO order_details (order_id, product_name, product_cost, sale_price, order_units) VALUES ('ORD0001234', 'Jewelry Cleaning Wipes', 0.85, 2.75, 3);

INSERT INTO order_details (order_id, product_name, product_cost, sale_price, order_units) VALUES ('ORD0007789', 'Silver Necklase w/Clover Charm', 8.15, 29.95, 1);
INSERT INTO order_details (order_id, product_name, product_cost, sale_price, order_units) VALUES ('ORD0007789', 'Gold Watch w/Leather Band', 12.50, 42.95, 1);

INSERT INTO order_details (order_id, product_name, product_cost, sale_price, order_units) VALUES ('ORD0009980', 'Platinum & Diamond Women''s Wedding Band', 13.10, 69.95, 1);
INSERT INTO order_details (order_id, product_name, product_cost, sale_price, order_units) VALUES ('ORD0009980', 'Jewelry Cleaning Solution', NULL, NULL, NULL);

This will give us three different orders with various products. Pay attention to the last INSERT statement. We have a NULL values for the cost, price, and number of units. This is important because we need to understand how using SUM with NULL values will work.

How to Run a SUM Query in SQL

Let’s start by using the built-in SUM function in SQL to see the total product cost, sale price, and number of units for each order. Remember that SUM is an aggregate function. That means that if we have any field that isn’t in an aggregate function, we will need to put that field in our GROUP BY clause.

SELECT order_id, SUM(product_cost) AS total_product_cost,
    SUM(sale_price) AS total_sale_price, SUM(order_units) AS total_units
FROM order_details
GROUP BY order_id
ORDER BY order_id;

In this case, we’re grouping on the order_id field because that’s not in an aggregate function. Here is what we get for the results:

order_id	total_product_cost	total_sale_price	total_units
ORD0001234	23.35	107.65	5
ORD0007789	20.65	72.9	2
ORD0009980	13.1	69.95	1

If we do the math, we’ll see that SQL has added up each line in each order for each column where we’re using SUM. To cross-check that, order #ORD0007789 has two line items – one with a cost of $8.15 and one with a cost of $12.50. If we add those up, we get $20.65.

But notice the last order. This order had two line items in it. But the cost, price, and units were all NULL for the second line item. So when we use the SUM function, those values are not included in the total. That’s because they can’t be – NULL is the lack of any value and so it can’t be used in a calculation like this.

Performing Calculations on Columns Within the SUM Function

You can also perform calculations within the SUM function and SQL will sum the net result of the calculations. For example, if we want to calculate profit margin on a given order we can subtract the cost from the price inside the SUM function, like this:

SELECT order_id, SUM(sale_price - product_cost) AS net_profit
FROM order_details
GROUP BY order_id
ORDER by order_id;

In this case, SQL will subtract the cost from the price for each row within an order (since we are grouping by the order_id) and then add up the result. Here is what we see:

order_id	net_profit
ORD0001234	84.3
ORD0007789	52.25
ORD0009980	56.85

Another way that we can accomplish the same thing is to independently sum up the price and the cost and subtract the result of the SUM functions themselves. This query gives us the same result:

SELECT order_id, SUM(sale_price) - SUM(product_cost) AS net_profit
FROM order_details
GROUP BY order_id
ORDER BY order_id;

In this instance, SQL will add up the value of the sale price and then the total value of the product cost first. Then it will subtract the product cost from the sale price to give us the profit.

Looking back at our first query, you might notice that we had an incorrect calculation. We were just summing the sale price and product cost but we didn’t factor in the number of units on each line item! This means we’re under-reporting our costs and profits. Let’s fix that with this next query. We can actually multiply within the SUM function to get the result we’re looking for:

SELECT order_id, SUM(product_cost * order_units) AS total_product_cost,
    SUM(sale_price * order_units) AS total_sale_price, SUM(order_units) AS total_units,
    SUM((sale_price * order_units) - (product_cost * order_units)) AS net_profit
FROM order_details
GROUP BY order_id
ORDER BY order_id;

The only order with a line item that has multiple units is #ORD0001234, which has 3 units of the “Jewelry Cleaning Wipes”. In this query, we perform a more complex calculation. We use parentheses within the last SUM function to specify the order of operations. We could also have formulated this as SUM((sale_price - product_cost) * order_units) to get the same result in a more compact manner. Here is what we see in our results either way:

order_id	total_product_cost	total_sale_price	total_units	net_profit
ORD0001234	25.05	113.15	5	88.1
ORD0007789	20.65	72.9	2	52.25
ORD0009980	13.1	69.95	1	56.85

Other Aggregate Functions

SUM is what we call an aggregate function. These are functions that perform calculations across the rows within a query. Remember that when we use an aggregate function, if we have any columns that are not contained within an aggregate, we must list those columns in the GROUP BY clause.

Our Introduction to SQL course covers aggregate functions in more detail. In this course, we go into average, minimum, maximum, and count functions to see in detail how we can use them for summarizing data in our database.