AWS

Redshift SUM Function

β€œThe Redshift SUM function is the aggregate function used to compute the sum of the input expressions in Redshift. It can compute the SUM of the input numeric expression or numeric column. The SUM function only supports the following data types as input expressions.

  • DECIMAL
  • NUMERIC
  • INTEGER
  • SMALLINT
  • BIGINT
  • DOUBLE
  • PRECISION

If we input the NULL value in the SUM function, it ignores the NULL value and calculates the sum of the remaining values or expressions. In this blog, we are going to discuss how the Redshift SUM function can be used in order to calculate the sum of the numeric values when it is passed as input expressions.”

Syntax to Use the SUM Function

The syntax to use the Redshift SUM function to compute the sum is as follows.

SELECT SUM ( [ ALL | DISTINCT ] expression )

The DISTINCT parameter is used when it is needed to compute the sum of only unique expressions in a column. It eliminates all the repetitive expressions while calculating the sum of the input expression.

The ALL parameter is used when it is needed to compute the sum of all input expressions, even if values are repetitive. By default, the ALL parameter is set.

The expression is the targeted column in the table that the function operates on to get the resultant sum.

Examples to Use the SUM Function

Now in this section, we will use some examples to explain how the SUM function can be used in Redshift in different scenarios to perform different tasks. Following are a few use case examples of the Redshift SUM function.

  • SUM function with ALL parameter
  • SUM function with DISTINCT parameter
  • SUM function with DEFAULT parameter
  • SUM function with NULL values
  • SUM function with WHERE statement
  • SUM function with GROUP BY statement

SUM Function With ALL Parameter

In this example, we will apply the SUM function to compute the sum using the ALL parameter on the table named database, as shown below. The below table includes four columns: ID, name, price and quantity.

ID Name Price Quantity
1 Keyboard 20 15
2 Mouse 15 20
3 Joystick 45 10
4 USB 60 25
5 Headphone 50 30

If we manually calculate the sum of the price column from the above table, it will be 190.

In order to compute the sum by using the SUM function, apply the SUM function to the price column.

SELECT SUM(ALL price)
FROM database

The SUM function will return the following sum of the price column of the database table.

sum
190

SUM Function With the DISTINCT Parameter

In this section, we will use the SUM function with the DISTINCT parameter in Redshift to calculate the sum of only distinct input parameters. The SUM function, when passed with the DISTINCT parameter, will only compute the sum of the unique expressions or values, neglecting the repetitive values.

ID Name Price Quantity
1 Keyboard 20 15
2 Mouse 15 20
3 Joystick 45 10
4 USB 60 20
5 Headphone 50 30

If we manually calculate the sum of the distinct values in the quantity column, we will get 60.

Apply the SUM function on the fourth column, i.e., quantity, to get the sum of unique values.

SELECT SUM(distinct quantity)
FROM database

The SUM function will return the following sum of the quantity column of the database table.

sum
60

SUM Function With Default Parameter

In this example, we will not pass any parameter to the SUM function to calculate the sum of input parameters, and the SUM function, by default, will use the ALL parameter to calculate the sum.

ID Name Price Quantity
1 Keyboard 20 10
2 Mouse 15 20
3 Joystick 45 10
4 USB 60 20
5 Headphone 50 30

If we manually calculate the sum of the ALL values in the quantity column, we will get 90.

SELECT SUM(quantity) as sum_quantity
FROM database

The SUM function will return the following sum of the quantity column of the database table.

sum_quantity
90

SUM Function With NULL Values

The SUM function does not consider the NULL expressions while calculating the sum and only computes the sum of the remaining NON_NULL expressions. In the following table named database, the quantity column includes some NULL values. Now we will use the SUM function on the quantity column to check the behavior of the SUM function.

ID Name Price Quantity
1 Keyboard 20 NULL
2 Mouse 15 20
3 Joystick 45 10
4 USB 60 20
5 Headphone 50 NULL

Apply the SUM function on the fourth column, i.e., the quantity of the database table, to get the sum.

SELECT SUM(ALL quantity) as sum_quantity
FROM database

The SUM function will return the following sum of the quantity column of the database table.

sum_quantity
50

SUM Function With WHERE Statement

The SUM function can also be used with the WHERE statement to apply some conditions to your query. The table, named database, is shown below, and we will calculate the sum of the price for only the tech category only.

ID Name Price Quantity Category
1 Keyboard 20 10 Tech
2 Mouse 15 20 Tech
3 Joystick 45 10 Clothing
4 USB 60 20 Tech
5 Headphone 50 30 Clothing

Now in order to compute the sum of the price for the Tech category, we will apply a WHERE statement along with the SUM function.

Apply the SUM function on the price column of the table, named database, using the WHERE statement to get the sum of only the Tech category.

select sum(ALL price) as sum_price
From database
where category='Tech';

The SUM function will return the following sum of the price column of the database table.

sum_price
95

SUM Function With GROUP BY Statement

The SUM function can be used with the GROUP BY statement to get the sum of your column in different groups depending upon another column.

Following is the table, named database, with different columns, and we will calculate the sum of the price for both tech and clothing categories using the SUM function with GROUP BY statement.

ID Name Price Quantity Category
1 Keyboard 20 10 Tech
2 Mouse 15 20 Tech
3 Joystick 45 10 Clothing
4 USB 60 20 Tech
5 Headphone 50 30 Clothing

Now in order to compute the sum of the price for both the Tech and clothing categories, we will apply a GROUP BY statement along with the SUM function.

Apply the SUM function on the price column of the table, named database, using the GROUP BY statement to get the sum of both the Tech and Clothing category.

SELECT category, SUM(price) AS total_price
FROM database
GROUP BY category;

The SUM function will return the following sum of the price column of the database table.

Conclusion

This article explains different ways to use the SUM function to calculate the sum of different input parameters. It calculates the sum of all the entries in a column by ignoring or considering the duplicate values depending upon the parameters passed. The SUM function can be used with the ALL or DISTINCT parameter to compute the sum of all the input expressions or unique expressions. It can also be used with the GROUP BY and WHERE STATEMENT to get the sum of selected entries from the column.

About the author

Zain Abideen

A DevOps Engineer with expertise in provisioning and managing servers on AWS and Software delivery lifecycle (SDLC) automation. I'm from Gujranwala, Pakistan and currently working as a DevOps engineer.