'Why does sum totals differ in query?

with totals as (select two_sim_ind,
    case when account_type_key in(86,88,13,113,37,39,131) then 'B2C' else 'B2B' end as Segment,
    case when subs_activation_date_key >= time_key then 'New' else 'Old' end as Sales_to_active,
    time_key as TIME_KEY, name_region as REGION, area_name as DISTRICT, sim_type as USIM_FLAG,
    case when lte_device = 1 or lte_ind in('Yes') or lte_user > 0 then '4G Device' 
    when ind_3g in('Yes') and lte_ind in('No') then '3G Device' else '2G Device' end as device_tech_support,
    device_type as DEVICE_TYPE,
    case when device_type in('Smartphone') then 'Yes' else 'No' end as is_Smartphone_AAB,
    case when data_user in(0) then 'No' else 'Yes' end as is_Data_User,
    case when lte_user in(0) then 'No' else 'Yes' end as is_4G_Data_User,
    count(distinct subs_key) as A1M,
    sum(total_revenue) as REVENUE_TOTAL,
    sum(data_traffic_local_2g + data_traffic_local_3g+data_traffic_local_4g) as TRAFFIC_DATA_full_Sum,
    sum(data_traffic_local_2g + data_traffic_local_3g) as g2_g3_traffic,
    sum(data_traffic_local_4g) as Data_traffic_4g
from dwh.m as m
left join (select model_name, ind_3g, lte_ind
from dwh.w) as devices
on m.model_name = devices.model_name
where rtc_active_ind > 0 and subs_status_key in ('A', 'S') 
and (time_key >= '2021-12-01' and time_key <= '2021-12-01')
group by two_sim_ind, 
    case when account_type_key in(86,88,13,113,37,39,131) then 'B2C' else 'B2B' end, 
    case when subs_activation_date_key >= time_key then 'New' else 'Old' end,
    case when data_user in(0) then 'No' else 'Yes' end,
    case when lte_device = 1 or lte_ind in('Yes') or lte_user > 0 then '4G Device' 
    when ind_3g in('Yes') and lte_ind in('No') then '3G Device' else '2G Device' end,
    time_key, name_region, area_name, sim_type, device_type, lte_user)

select REGION, sum(REVENUE_TOTAL)
from totals
group by REGION

When I aggregate total of all records by region, value is significantly higher than it should be.

Result I get:
enter image description here

select name_region as REGION, sum(total_revenue)
from dwh.m
where (time_key >= '2021-12-01' and time_key <= '2021-12-01')
and rtc_active_ind > 0 and subs_status_key in ('A', 'S')
group by name_region;

Result of query and what I need:
enter image description here

Something happens to individual records when grouping by? What could be the reason?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source