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?
Answer
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.
🔹 To return 0 instead of NULL, use COALESCE(), which replaces NULL with a default value:
SELECT COALESCE(SUM(amount), 0) AS total_sales
FROM sales
WHERE region = 'West';
🔹 This ensures that when SUM(amount) is NULL, it gets converted to 0.
(copied to https://stackoverflow.com/questions/79524739/why-does-sum-return-null-instead-of-0 )