-1

I am trying to left join two tables and as left join works - it should output all data from left and matched result from the right (even null values). But Dbeaver cuts all null values so that instead of having approximately 170 000 gross sales, I have only 130 000. Pls.help to fix query.

select
i.market_name,
i.date_key,
i.dil_channel,
sum(ceil(d.total_revenue)) as revenue_sum,
count(distinct(i.subs_key)) as gross_sales
from dwh.vw_fct_subs_inflow_aab as i
left join 
dwh.vw_dm_subs_kpi_daily as d
on i.subs_key=d.subs_key and i.ban_key=d.ban_key
where d.subs_activation_date_key between '2020-07-01' and '2020-07-21'
and d.time_key between '2020-07-01' and '2020-07-21'
and i.date_key between '2020-07-01' and '2020-07-21'
and i.segment_key = 'NW1'
group by i.market_name,
i.date_key,
i.dil_channel
Kamila Nurlanova
  • 35
  • 1
  • 6
  • Kindly edit the question & format the texts in better way. (Like what is your expected scenario & what is actually happening) So that in future if anyone facing the same issue, they can make use of it. – Ganesh Jul 23 '20 at 02:19

1 Answers1

2

Your where clause restricts rows based on d.subs_activation_date_key and d.time_key.

When there are no matching rows in vw_fct_subs_inflow_aab (aliased as d), the values of those columns are null, and null will never match the between conditions.

Should those tests be moved to the left join condition?

Example:

select i.market_name,
       i.date_key,
       i.dil_channel,
       sum(ceil(d.total_revenue)) as revenue_sum,
       count(distinct(i.subs_key)) as gross_sales
  from dwh.vw_fct_subs_inflow_aab as i
  left join dwh.vw_dm_subs_kpi_daily as d
         on i.subs_key=d.subs_key and i.ban_key=d.ban_key
        and d.subs_activation_date_key between '2020-07-01' and '2020-07-21'
        and d.time_key between '2020-07-01' and '2020-07-21'
 where i.date_key between '2020-07-01' and '2020-07-21'
   and i.segment_key = 'NW1'
 group by i.market_name, i.date_key, i.dil_channel
Mike Organek
  • 11,647
  • 3
  • 11
  • 26
  • Hey, i tried to put all conditions into left join but it gives an **error: column d.total_revenue does not exist**. – Kamila Nurlanova Jul 22 '20 at 17:11
  • `select i.market_name, i.date_key, i.dil_channel, sum(ceil(d.total_revenue)) as revenue_sum, count(distinct(i.subs_key)) as gross_sales from dwh.vw_fct_subs_inflow_aab as i left join ( select subs_key, ban_key, sum(ceil(total_revenue)) from dwh.vw_dm_subs_kpi_daily where subs_activation_date_key between '2020-07-01' and '2020-07-21' and time_key between '2020-07-01' and '2020-07-21' group by subs_key, ban_key ) as d on i.subs_key=d.subs_key and i.ban_key=d.ban_key where i.date_key between '2020-07-01' and '2020-07-21' and i.segment_key = 'NW1'` – Kamila Nurlanova Jul 22 '20 at 17:12
  • @KamilaNurlanova I updated my answer to demonstrate what I meant. – Mike Organek Jul 22 '20 at 17:16