SUM and CASE in Snowflake SQL

In this article, we’ll explore how to perform sum calculations with conditional logic using the SUM and CASE functions in Snowflake SQL.

Problem Statement

You have a report that is created based on a join of 5 tables. With the join of the tables, you perform some calculations, group by (roll up) and some other stuff: You need to check if the cases number is greater than or equals to 3 and flag it. Your ideal final result would be instead of yes and no, could also return 1 and 0.

SQL Solution

To solve this problem, you can use a combination of the SUM function and the CASE function in Snowflake SQL.

SELECT 
    supervisor, 
    dept,
    SUM(column1/column2) as "Case",
    iff("Case" >= 3, 'Yes', 'No') as flag
FROM values
    (30,3, 'a', 'c'),
    (10,2, 'b', 'b'),
    (8,4, 'a', 'b')
    as t(column1, column2, supervisor, dept )
GROUP BY rollup (1,2);

This query will produce the following result:

Explanation

The SUM function calculates the sum of the division of column1 and column2 for each row.

The CASE function is used to check if the result of the sum calculation is greater than or equal to 3. If it is, then the flag is set to ‘Yes’, otherwise it’s set to ‘No’.

The IFF function is used to concisely write this conditional statement: iff(result >= 3, 'Yes', 'No').

Use Cases

This solution can be applied in a variety of scenarios where you need to perform calculations with conditional logic. For example, if you’re analyzing sales data and want to identify the days when sales were greater than or equal to $1000.

SELECT 
    order_date,
    SUM(order_total) as total_sales
FROM orders
WHERE order_date >= '2022-01-01'
GROUP BY rollup (order_date)
HAVING SUM(order_total) >= 1000;

In this example, we’re using the SUM function to calculate the total sales for each day. We then use the CASE function to check if the result is greater than or equal to $1000 and filter out any rows that don’t meet this condition.

Conclusion

In conclusion, the SUM and CASE functions in Snowflake SQL can be used together to perform calculations with conditional logic. By using these functions, you can simplify your queries and make them more efficient.

Last modified on 2024-04-24