SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table TABLE1 (table1_id int);
create table TABLE2 (table2_id int, table1_id int, earning money);
create table TABLE3 (table3_id int, table1_id int);

insert into TABLE1 values (1);
insert into TABLE2 values (49, 1, 10000); -- Expected sum = 10000
insert into TABLE3 values (991, 1); -- Expected count = 3
insert into TABLE3 values (992, 1);
insert into TABLE3 values (993, 1);

insert into TABLE1 values (2);
insert into TABLE2 values (21, 2, 3000); -- Expected sum = 7000.00
insert into TABLE2 values (22, 2, 4000);
insert into TABLE3 values (201, 2); -- Expected count = 5
insert into TABLE3 values (202, 2);
insert into TABLE3 values (203, 2);
insert into TABLE3 values (204, 2);
insert into TABLE3 values (205, 2);

13 rows affected

-- Inflated results due to
-- multiple independene one-to-many joins
select 
    T1.table1_id, SUM(T2.earning) AS Earning, COUNT(T3.table1_id) AS T3Count
from 
    TABLE1 T1
inner join 
    TABLE2 T2 on T2.table1_id = T1.table1_id
inner join 
    TABLE3 T3 on T3.table1_id = T1.table1_id
group by 
    T1.table1_id;


table1_id
Earning
T3Count


1
30000.0000
3

2
35000.0000
10


-- Fixed using CROSS APPLY
select 
    T1.table1_id, CA1.Earning, CA2.T3Count
from 
    TABLE1 T1
cross apply (
    select SUM(earning) AS Earning
    from TABLE2 T2
    where T2.table1_id = T1.table1_id
) CA1
cross apply (
    select COUNT(*) AS T3Count
    from TABLE3 T3
    where T3.table1_id = T1.table1_id
) CA2


table1_id
Earning
T3Count


1
10000.0000
3

2
7000.0000
5


-- Fixed using subqueries
select 
    T1.table1_id, SS1.Earning, SS2.T3Count
from 
    TABLE1 T1
inner join (
    select T2.table1_id, SUM(earning) AS Earning
    from TABLE2 T2
    group by T2.table1_id
) SS1 on SS1.table1_id = T1.table1_id
inner join (
    select T3.table1_id, COUNT(*) AS T3Count
    from TABLE3 T3
    group by T3.table1_id
) SS2 on SS2.table1_id = T1.table1_id


table1_id
Earning
T3Count


1
10000.0000
3

2
7000.0000
5