I'm running a SUM() query on a Snowflake table, but instead of returning 0 when there are no matching rows, it returns NULL.

For example, I have this table:

CREATE OR REPLACE TABLE sales (
    region STRING,
    amount NUMBER
);

INSERT INTO sales VALUES 
    ('North', 100),
    ('South', 200),
    ('North', NULL),
    ('East', 150);

Now, I run the following query to sum the sales for a region that doesn’t exist:

SELECT SUM(amount) FROM sales WHERE region = 'West';

• Expected output: 0
• Actual output: NULL ❌

Why is this happening, and how can I make Snowflake return 0 instead of NULL?

I'm running a SUM() query on a Snowflake table, but instead of returning 0 when there are no matching rows, it returns NULL.

For example, I have this table:

CREATE OR REPLACE TABLE sales (
    region STRING,
    amount NUMBER
);

INSERT INTO sales VALUES 
    ('North', 100),
    ('South', 200),
    ('North', NULL),
    ('East', 150);

Now, I run the following query to sum the sales for a region that doesn’t exist:

SELECT SUM(amount) FROM sales WHERE region = 'West';

• Expected output: 0
• Actual output: NULL ❌

Why is this happening, and how can I make Snowflake return 0 instead of NULL?

• snowflake-cloud-data-platform

Share Improve this question Follow edited Mar 21 at 7:36 DarkBee 15.5k88 gold badges7272 silver badges117117 bronze badges asked Mar 21 at 6:55 Jonathan LalouJonathan Lalou 35411 gold badge33 silver badges1010 bronze badges 1

• Yes - that's is the expected default behavior for every system using SQL .... if there is no value at all - then you get back NULL - that's what it's defined to be. And applying any operators to NULL will always again result in NULL - you cannot "sum up", or get a "max" from nothing ..... you'll just get back nothing again – marc_s Commented Mar 21 at 7:05

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

First and basic approach: explicitly filter out NULL before aggregation:

SELECT SUM(CASE WHEN amount IS NOT NULL THEN amount ELSE 0 END) AS total_sales 
FROM sales 
WHERE region = 'West'; 

This method ensures that NULL does not interfere with the SUM calculation.

✅ Even better: Use COALESCE() to handle NULL.

By default, SUM() returns NULL if there are no rows that match the condition or if all matching rows contain NULL.