I'm trying to use Sum() over(partition by ) to find the difference between two groups of numbers and I'm getting an unexpected result

Question

I'm using sql and tableau to create a burn down chart. I wrote a sql query that does most of the calculation for me. I have a table structure that looks like this

ld.cal_dt	ld.camp_name	ld.ld_cnt	dy_cmp.cal_dt	dy_cmp.camp_name	dy_comp.com_cnt	brndn
2023-09-05	Ex-000010	62	NULL	NULL	NULL	NULL
2023-09-06	Ex-000010	0	2023-09-06	Ex-000010	54	-54
2023-09-08	Ex-000010	0	2023-09-08	Ex-000010	1	-55

Below is what the relevant part of my query looks like

select  lc.calendardate,
        lc.campaign_name,
        lc.loaded_count,
        dcc.calendardate,
        dcc.campaign_name,
        dcc.completed_call_count,
        sum(cast(lc.loaded_count as int) - cast(dcc.completed_call_count as int)) over(partition by lc.campaign_name order by lc.calendardate asc) as burn_down
from adjusted_loaded_count as lc
left join adjusted_daily_calls_completed as dcc on
    lc.calendardate = dcc.calendardate and
    lc.campaign_name = dcc.campaign_name
where lc.campaign_name is not null

I'm getting unexpected results in my burndown column. I expect the result to be 8 and then 7 instead of -54 and -55. Why aren't I actually getting the difference between the two numbers?

Answer 1

Three rows:

ld_cnt	com_cnt	diff	sum so far
62	NULL	NULL	NULL
0	54	-54	-54
0	1	-1	-55

You want

ld_cnt	com_cnt	diff	sum so far
62	treat NULL as 0	0	62
0	54	-54	8
0	1	-1	7

You want to treat NULLs as zeros. Use COALESCE for this:

SUM(
  COALESCE(CAST(lc.loaded_count as int), 0) - 
  COALESCE(CAST(dcc.completed_call_count as int), 0)
) OVER (PARTITION BY lc.campaign_name 
        ORDER BY lc.calendardate ASC) AS burn_down