- 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.
FROM database
The SUM function will return the following sum of the price column of the database table.
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.
FROM database
The SUM function will return the following sum of the quantity column of the database table.
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.
FROM database
The SUM function will return the following sum of the quantity column of the database table.
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.
FROM database
The SUM function will return the following sum of the quantity column of the database table.
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.
From database
where category='Tech';
The SUM function will return the following sum of the price column of the database table.
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.
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.
