👀 Views: 0 💬 Answers: 1 📅 Created: 2025-06-28

postgresql window-functions null SQL

I'm prototyping a solution and I'm encountering an issue when using window functions in PostgreSQL 13... I'm trying to calculate a running total of sales grouped by department, but I noticed that when a department has NULL values in the sales column, the running total does not seem to be calculating correctly. My query looks like this: ```sql SELECT department, sales, SUM(sales) OVER (PARTITION BY department ORDER BY transaction_date) AS running_total FROM sales_data ORDER BY department, transaction_date; ``` In my sales_data table, I have some records where the sales column is NULL for certain transactions. However, instead of treating NULL as zero in the context of the running total, it appears to be skipping those rows altogether, resulting in an incorrect running total. I've tried using COALESCE to replace NULLs with zero like this: ```sql SELECT department, COALESCE(sales, 0) AS sales, SUM(COALESCE(sales, 0)) OVER (PARTITION BY department ORDER BY transaction_date) AS running_total FROM sales_data ORDER BY department, transaction_date; ``` However, the running total is still not what I expect. For example, if I have department A with transactions of 100, NULL, and 200, I would expect the running total to be 100, 100, and 300, but instead, I get 100, NULL, and 300. I've also checked for any filters in the code that might be excluding NULL values, but I don't see any. Is there something I'm missing with how window functions handle NULLs, or is there another approach I should consider? Any guidance would be greatly appreciated! Thanks for taking the time to read this!