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.