**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.

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.