CODEWITHSUNDEEP
sqlsql-serverselectgroup-byleft-join
Nezreli
Nezreli

Reputation: 1288

SQL query for summing the columns of multiple tables

I thought that this would be an easy one but it baffles me and I don't know the proper solution. Let me represent the problem. Here are the tables and the data:

create table table1 (ID int primary key, total int);
insert into table1 values (1, 100);
insert into table1 values (2, 150);

create table table2 (ID int primary key, table1_ID int, total int);
insert into table2 values(1, 1, 10);
insert into table2 values(2, 1, 20);
insert into table2 values(3, 1, 40);

create table table3 (ID int primary key, table2_ID int, total int);
insert into table3 values(1, 1, 1000);
insert into table3 values(2, 1, 1000);
insert into table3 values(3, 2, 2000);
insert into table3 values(4, 2, 2000);

I need to write a SQL query which will list table1 along with summed totals for table 2 and table 3 if there is any totals.

If it's only table1 and table2 then this query works fine:

select table1.ID, table1.total, sum(table2.total) from table1
left outer join table2 on table1.ID = table2.table1_ID
group by table1.ID, table1.total;

The results are correct:
1 100 70
2 150 null

But, if I try to include table3:

select table1.ID, table1.total, sum(table2.total), sum(table3.total) from table1
left outer join table2 on table1.ID = table2.table1_ID
left outer join table3 on table2.ID = table3.table2_ID
group by table1.ID, table1.total

I get this result:
1 100 100 6000
2 150 null null

Sum of table 2 is not correct because join with table 3 multiplied its rows and the value 10 and value 20 were summed twice. How can I correct this SQL query?

In my real life example I actually have table4 and table5.

Upvotes: 1

Views: 2149

Answers (4)

Dudi Konfino
Dudi Konfino

Reputation: 1136

for sql server 2012

    DECLARE @x table(id int,total int,stotal int)
    INSERT INTO @x
    SELECT id,total,sum(total)
    FROM table1 
    UNION
    SELECT id,total,sum(total)
    FROM table2
    UNION
    SELECT id,total,sum(total)
    FROM table3

    SELECT id,total,lead(sum(stotal))
    FROM @x

Upvotes: 0

Sandeep
Sandeep

Reputation: 1210

SELECT 
    table1.ID, 
    table1.total,
    total2,
    total3
FROM 
    table1
CROSS APPLY
(
    SELECT
        SUM(table2.total) as total2
    FROM
        table2
    WHERE 
        table1_ID = table1.ID
) AS T1
CROSS APPLY
(
    SELECT 
        SUM(table3.total) as total3
    FROM
        table3
    INNER JOIN
        table2
    ON
        table3.table2_ID = table2.ID
    WHERE
        table2.table1_ID = table1.ID
) AS T2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You need to pre-aggregate the values along each dimension. You are getting a cartesian product, which throws off the sums:

select t1.ID, t1.t1_total, t2.t2_total, t3.t3_total
from table1 t1 left join
     (select t2.table1_id, sum(t2.total) as t2_total
      from table2 t2
      group by t2.table1_id
     ) t2
     on t1.id = t2.table1_id left join
     (select t2.table1_id, sum(t3.total) as t3_total
      from table2 t2 join
           table3 t3
           on t2.id = t3.table2_id
      group by t2.table1_id
     ) t3
     on t1.id = t3.table1_id;

In other words, get everything to the level of table1.id before doing the joins, so the join does not multiply the number of rows unnecessarily.

Upvotes: 2

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT t1.ID, t1.total, 
       SUM(ISNULL(t2.total, 0)) AS t2Total, 
       SUM(ISNULL(t3.total, 0)) AS t3Total 
FROM table1 t1
LEFT OUTER JOIN table2 t2 ON t1.ID = t2.table1_ID
LEFT OUTER JOIN (SELECT t3.table2_ID, SUM(t3.total) AS total 
                 FROM table3 t3
                 GROUP BY t3.table2_ID
                ) AS t3 ON t2.ID = t3.table1_ID
GROUP BY t1.ID, t1.total;

Upvotes: 1

Related Questions