sqlanalyticswindow-functionsimpala

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

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?

Solution

  • 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