How to Sum Two Columns in an SQL Query?

1. Overview

All relational databases support summing two or more table columns/fields in a single SQL query. Summing of column values is typically used for numeric data (int, decimal, float, double). The summing of column values arithmetically adds the values and returns the sum.

In this tutorial, we’ll learn about the options for summing the values of two columns.

2. Setting up the Schema

All the SQL scripts in this tutorial can be used with databases; MySQL, PostgreSQL, and SQL Server. To demonstrate, we’ll create a new table containing numeric data in two of its columns. The example table contains quarterly publisher/magazine sales data. We’ll keep the two columns to sum non-nullable in the first set of examples:

CREATE TABLE magazine 
(publisher_id INT NOT NULL, 
magazine_name varchar(50), 
q1sales INT NOT NULL,
q2sales INT NOT NULL);

Let’s add sample data:

INSERT INTO magazine
(publisher_id,magazine_name,q1sales,q2sales)
VALUES(1,'magazine_1',60,30),
(1,'magazine_2',100,25),
(2,'magazine_3',50,25),
(2,'magazine_4',75,20),
(3,'magazine_5',10,50),
(3,'magazine_6',25,25);

Let’s query the table data:

SELECT * FROM magazine;

The result set lists the individual column values:

+--------------+---------------+---------+---------+
| publisher_id | magazine_name | q1sales | q2sales |
+--------------+---------------+---------+---------+
|            1 | magazine_1    |      60 |      30 |
|            1 | magazine_2    |     100 |      25 |
|            2 | magazine_3    |      50 |      25 |
|            2 | magazine_4    |      75 |      20 |
|            3 | magazine_5    |      10 |      50 |
|            3 | magazine_6    |      25 |      25 |
+--------------+---------------+---------+---------+

We’ll be summing the columns q1sales and q2sales.

3. Sum Using a Column Expression

We can sum up two columns by using an expression that adds the values in the two columns. Let’s sum q1sales and q2sales values:

SELECT  
publisher_id, magazine_name,
q1sales+q2sales AS magazine_sales 
FROM magazine;

It sums the two quarterly sales columns in the result set:

+--------------+---------------+----------------+
| publisher_id | magazine_name | magazine_sales |
+--------------+---------------+----------------+
|            1 | magazine_1    |             90 |
|            1 | magazine_2    |            125 |
|            2 | magazine_3    |             75 |
|            2 | magazine_4    |             95 |
|            3 | magazine_5    |             60 |
|            3 | magazine_6    |             50 |
+--------------+---------------+----------------+

Indeed, we get an arithmetic sum of two columns.

4. Sum Using the SUM() Function

We can use the SUM() aggregate function to derive the sum of two columns. The terms “aggregate” and “sum” in the context of generating an arithmetic sum have the same meaning; however, “aggregate” in SQL is usually used to generate sums over subsets of data by grouping data. Let’s remember that an aggregate function such as SUM() is typically used with the GROUP BY clause to group values into subsets.

4.1. Aggregate Columns Separately

When we want to aggregate the two columns individually and add the two sums using an expression we should use the SUM() function. We perform single-column sums for two columns grouped by an id column using the GROUP BY clause. Let’s aggregate the q1sales and q2sales columns individually in an SQL query and add the individual sums using an additive expression:

SELECT
publisher_id,magazine_name,
SUM(q1sales) + SUM(q2sales) AS Q1Q2Sales
FROM magazine
GROUP BY publisher_id,magazine_name;

The result set lists the arithmetic sum of aggregations for individual columns:

+--------------+---------------+-----------+
| publisher_id | magazine_name | Q1Q2Sales |
+--------------+---------------+-----------+
|            1 | magazine_1    |        90 |
|            1 | magazine_2    |       125 |
|            2 | magazine_3    |        75 |
|            2 | magazine_4    |        95 |
|            3 | magazine_5    |        60 |
|            3 | magazine_6    |        50 |
+--------------+---------------+-----------+

In this example, we first get a quarterly aggregation of sales for each of the publishers/magazines and then add the two quarterly results.

4.2. Aggregate Columns Together

When we want to aggregate two columns together, we should again use the SUM() function along with the GROUP BY clause. Let’s sum the q1sales and q2sales columns again; however, using an additive expression within the SUM() function call to aggregate them together this time:

SELECT  
publisher_id, magazine_name,
SUM(q1sales + q2sales) AS Q1_Q2_Sales
FROM magazine
GROUP BY publisher_id,magazine_name;

We get an aggregated sum of Q1 + Q2 sales:

+--------------+---------------+-------------+
| publisher_id | magazine_name | Q1_Q2_Sales |
+--------------+---------------+-------------+
|            1 | magazine_1    |          90 |
|            1 | magazine_2    |         125 |
|            2 | magazine_3    |          75 |
|            2 | magazine_4    |          95 |
|            3 | magazine_5    |          60 |
|            3 | magazine_6    |          50 |
+--------------+---------------+-------------+

Notably, the sum is the same as when we used the SUM() function on the two columns individually. However, this may not always be true.

5. Sum Using SUM() as a Window Function

SQL specification defines a Window function as one that performs a calculation over a group/set of rows related to the current query row. The window calculation is performed for each row of data. Further, while non-Window functions operate only on the data about a single row of data, Window functions operate over a “window” of data when performing a SQL query, and that too for each query row.

5.1. The OVER and WINDOW Clauses

We use the OVER and WINDOW clauses when we use SUM() as a Window function.

To elaborate, the OVER clause specifies the window of data to perform a related calculation. The OVER clause could make use of the PARTITION BY clause to group rows over which to perform a related calculation. Further, the OVER clause may select a subset within the current partition using a data frame defined by ROWS, or RANGE clauses.

Additionally, we could use the OVER clause to specify a named window of data. When we use a named window of data, we use the WINDOW clause to define that window of data.

5.2. An Example

Let’s use the same data set to sum two column values using SUM() as a Window function.

This time, we’re interested in aggregated sales generated for each magazine over the two quarters Q1 and Q2. Furthermore, we use SUM() as a Window function to include two other result set columns. The publisher_sales column is for the total magazine sales for a publisher; its value stays the same for a specific publisher. The total_sales column is for the total magazine sales across all publishers; its value stays the same for each row of data in the result set.

Let’s demonstrate with an example SQL query:

SELECT publisher_id,magazine_name,q1sales+q2sales AS magazine_sales, 
SUM(q1sales+q2sales) 
    OVER(PARTITION BY publisher_id) AS publisher_sales, 
SUM(q1sales+q2sales) 
    OVER() AS total_sales 
FROM magazine 
WINDOW w AS (PARTITION BY publisher_id ORDER BY publisher_id);

The result set lists the sum of two columns using partitions:

+--------------+---------------+----------------+-----------------+-------------+
| publisher_id | magazine_name | magazine_sales | publisher_sales | total_sales |
+--------------+---------------+----------------+-----------------+-------------+
|            1 | magazine_1    |             90 |             215 |         495 |
|            1 | magazine_2    |            125 |             215 |         495 |
|            2 | magazine_3    |             75 |             170 |         495 |
|            2 | magazine_4    |             95 |             170 |         495 |
|            3 | magazine_5    |             60 |             110 |         495 |
|            3 | magazine_6    |             50 |             110 |         495 |
+--------------+---------------+----------------+-----------------+-------------+

The first OVER() clause partitions rows by publisher_id and performs a calculation over the partition returning the sales sum by publisher. The second OVER() clause selects the complete query set as a single partition.

6. Handling NULL Values

When we want to sum two columns that have some of the values as NULL, the results could be markedly different. To demonstrate, let’s create a new table magazine_nullable that allows null values, and add a dataset with some NULL values:

CREATE TABLE magazine_nullable 
(publisher_id INT NOT NULL, 
magazine_name varchar(50), 
q1sales INT,
q2sales INT);

INSERT INTO magazine_nullable
(publisher_id,magazine_name,q1sales,q2sales)
VALUES(1,'magazine_1',NULL,30),
(1,'magazine_2',100,NULL),
(2,'magazine_3',50,NULL),
(2,'magazine_4',NULL,20),
(3,'magazine_5',NULL,50),
(3,'magazine_6',NULL,25);

Let’s discuss how to sum columns that may contain some NULL values. At the outset, we can’t sum up two columns by using a column expression because if an expression such as col1+col2 contains a NULL value, the result of evaluating the expression is NULL, which won’t give accurate results.

6.1. Using the SUM() Function

We can benefit from using the SUM() function when some column values are NULL. Notably, the SUM() function ignores the NULL values and uses the non-NULL value/s to get the sum. When we want to sum two nullable columns when only some of the values in a group are NULL, we should use the SUM() function along with the GROUP BY clause and a WHERE clause if needed. Let’s sum sales for publishers 1 and 2 aggregated over Q1 and over Q2 using the SUM() function:

SELECT 
publisher_id, 
SUM(q1sales) + SUM(q2sales) AS Q1Q2Sales
FROM magazine_nullable WHERE publisher_id<3
GROUP BY publisher_id, magazine_name;

In this example, we didn’t get any NULL values:

+--------------+-----------+
| publisher_id | Q1Q2Sales |
+--------------+-----------+
|            1 |       130 |
|            2 |        70 |
+--------------+-----------+

Similarly, let’s sum sales for publisher 3 aggregated over Q1 and over Q2:

SELECT 
publisher_id, 
SUM(q1sales) + SUM(q2sales) AS Q1Q2Sales
FROM magazine_nullable WHERE publisher_id=3
GROUP BY publisher_id;

This time, we get NULL even though publisher 3 had sales in Q2:

+--------------+-----------+
| publisher_id | Q1Q2Sales |
+--------------+-----------+
|            3 |      NULL |
+--------------+-----------+

We get NULL because the SUM() function returns NULL when all column values are NULL, and further if an expression such as expr1+expr2 has one or both of the sub-expressions as NULL, the result of adding them is NULL.

Therefore, this summation approach isn’t suitable when one of the two columns to be summed has all NULL values. Let’s discuss how to sum up two columns when one of the columns has all NULL values.

6.2. Using the COALESCE() Function

When we want to sum two nullable columns with one or both columns having all NULL values, we should use the COALESCE() function. The COALESCE() function accepts multiple arguments and returns the first non-null argument. We add one of the arguments to COALESCE() as 0 so that the function returns 0 when it finds all other values NULL. To demonstrate, let’s rerun the same query; this time with the SUM() function call within the COALESCE() function:

SELECT
publisher_id,magazine_name,
SUM(COALESCE(q1sales,0)) + SUM(COALESCE(q2sales,0)) AS Q1Q2Sales
FROM magazine_nullable
GROUP BY publisher_id,magazine_name;

Accordingly, the COALESCE() function returns 0 when it finds all other values NULL:

+--------------+---------------+-----------+
| publisher_id | magazine_name | Q1Q2Sales |
+--------------+---------------+-----------+
|            1 | magazine_1    |        30 |
|            1 | magazine_2    |       100 |
|            2 | magazine_3    |        50 |
|            2 | magazine_4    |        20 |
|            3 | magazine_5    |        50 |
|            3 | magazine_6    |        25 |
+--------------+---------------+-----------+

Furthermore, we can use the SUM() as a Window function along with the COALESCE() function:

SELECT  publisher_id,magazine_name,
COALESCE(q1sales,0)+COALESCE(q2sales,0) AS magazine_sales,
SUM(COALESCE(q1sales,0)+COALESCE(q2sales,0))
    OVER(PARTITION BY publisher_id) AS publisher_sales,
SUM(COALESCE(q1sales,0)+COALESCE(q2sales,0))
    OVER() AS total_sales
FROM magazine_nullable
WINDOW w AS (PARTITION BY publisher_id ORDER BY publisher_id);

Again, the COALESCE() function should return 0 when it finds all other values to be NULL:

+--------------+---------------+----------------+-----------------+-------------+
| publisher_id | magazine_name | magazine_sales | publisher_sales | total_sales |
+--------------+---------------+----------------+-----------------+-------------+
|            1 | magazine_1    |             30 |             130 |         275 |
|            1 | magazine_2    |            100 |             130 |         275 |
|            2 | magazine_3    |             50 |              70 |         275 |
|            2 | magazine_4    |             20 |              70 |         275 |
|            3 | magazine_5    |             50 |              75 |         275 |
|            3 | magazine_6    |             25 |              75 |         275 |
+--------------+---------------+----------------+-----------------+-------------+

Indeed, we get an accurate summation.

7. Conclusion

In this article, we learned about summing two columns in an SQL query. We discussed two methods for summing non-nullable columns; an additive column expression and the SUM() function. Furthermore, we discussed using SUM() as a Window function. When column values could be NULL, we should use the SUM() function or the COALESCE() function.