Multiple Sum Aggregates of Multiple Sub Tables

Question

Say for example that I have the following tables:

| TableA 
+-------------------------------------------------------------+
| FIELD     | TYPE    | NULL | KEY | DEFAULT | Extra          |
+-------------------------------------------------------------+
| TableA_ID | Int     | NO   | PK  | NULL    | Auto Increment |
| Name      | Varchar | No   |     | NULL    |                |
+-------------------------------------------------------------+

| TableB
+-------------------------------------------------------------+
| FIELD     | TYPE    | NULL | KEY | DEFAULT | Extra          |
+-------------------------------------------------------------+
| TableB_ID | Int     | NO   | PK  | NULL    | Auto Increment |
| TableA_ID | Int     | No   | FK  | NULL    |                |
| Qty       | Decimal | No   |     | NULL    |                |
+-------------------------------------------------------------+

| TableC
+-------------------------------------------------------------+
| FIELD     | TYPE    | NULL | KEY | DEFAULT | Extra          |
+-------------------------------------------------------------+
| TableC_ID | Int     | NO   | PK  | NULL    | Auto Increment |
| TableA_ID | Int     | No   | FK  | NULL    |                |
| Qty       | Decimal | No   |     | NULL    |                |
+-------------------------------------------------------------+

with the following rows:

| Table A
+-------------------------+
| TableA_ID | Name        |
+-------------------------+
| 1         | "Record AA" |
| 2         | "Record AB" |
| 3         | "Record AC" |
| 4         | "Record AD" |
| 5         | "Record AE" |
| 6         | "Record AF" |
+-------------------------+

| TableB
+-----------------------------+
| TableB_ID | TableA_ID | Qty |
+-----------------------------+
| 1         | 1         | 5   |
| 2         | 1         | 5   |
| 3         | 2         | 7.5 |
| 4         | 2         | 8   |
| 5         | 2         | 11  |
| 6         | 3         | 3   |
| 7         | 4         | 5   |
| 8         | 5         | 9   |
| 9         | 5         | 13  |
| 10        | 5         | 79  |
+-----------------------------+

| TableC
+------------------------------+
| TableC_ID | TableA_ID | Qty  |
+------------------------------+
| 1         | 1         | 7    |
| 2         | 1         | 7    |
| 3         | 2         | 2.25 |
| 4         | 2         | 6    |
| 5         | 2         | 13   |
| 6         | 3         | 4    |
| 7         | 4         | 19   |
| 8         | 5         | 2    |
| 9         | 6         | 19.5 |
| 10        | 6         | 51   |
+------------------------------+

I started out with the following query:

SELECT Name,
    ISNULL(SUM(TableB.QTY),0.0),
    ISNULL(SUM(TableC.QTY),0.0)
FROM TableA
    LEFT OUTER JOIN TableB ON TableA.TableA_ID = TableB.TableA_ID
    LEFT OUTER JOIN TableC ON TableA.Table_ID = TableC.TableA_ID
GROUP BY Name

The problem with this query is that it gives me incorrect summations. I then moved on to sub-queries such as below:

SELECT Name,
    ISNULL([SubTable1],0.0) TableB_TotalQty,
    ISNULL([SubTable2],0.0) TableC_TotalQty
FROM TableA
    LEFT OUTER JOIN
    (
        SELECT TableA_ID, SUM(Qty) [TotalQty]
        FROM TableB
        GROUP BY TableA_ID
    ) [SubTable1] ON TableA.TableA_ID = [SubTable1].TableA_ID
    LEFT OUTER JOIN
    (
        SELECT TableA_ID, SUM(Qty) [TotalQty]
        FROM TableC
        GROUP BY TableA_ID
    ) [SubTable2] ON TableA.TableA_ID = [SubTable2].TableA_ID

Now are there any other ways to return the correct information other than using sub-queries?

Answer 1

This is really two separate queries for each column. I like @DimaSUN's solution that use OUTER APPLY. For older versions of SQL Server (<2005), combining two separate queries for each column should also work:

select name, SumB, SumC from
    (select a.tableA_ID, a.name, isnull(sum(b.qty),0) as [SumB] 
         from tableA a left join tableB b 
             on a.tableA_ID = b.tableA_ID 
         group by a.tableA_ID, a.name) q1
  inner join 
    (select a.tableA_ID, isnull(sum(c.qty),0) as [SumC] 
         from tableA a left join tableC c 
             on a.tableA_ID = c.tableA_ID 
         group by a.tableA_ID) q2
  on q1.tableA_ID = q2.tableA_ID
  order by name;

Another alternative, using Common Table Expressions (>= SQL 2005):

with CTE_TB as (
                select a.tableA_ID, a.name, isnull(sum(b.qty),0) as [SumB] 
                    from tableA a left join tableB b 
                    on a.tableA_ID = b.tableA_ID 
                    group by a.tableA_ID, a.name),
     CTE_TC as (select a.tableA_ID, isnull(sum(c.qty),0) as [SumC] 
                    from tableA a left join tableC c 
                    on a.tableA_ID = c.tableA_ID 
                    group by a.tableA_ID)
select name, SumB, SumC from CTE_TB a inner join CTE_TC b on a.tableA_ID=b.tableA_ID
      order by name;

Answer 2

SELECT TA.Name, SumB =  isnull(TB.SumB,0),  SumC =  isnull(TC.SumC,0)
FROM TableA TA
 outer apply 
        ( select SumB = sum(Qty) 
          from TableB 
          where TableA_ID = TA.TableA_ID ) TB
 outer apply 
        ( select SumC = sum(Qty) 
          from TableC 
          where TableA_ID = TA.TableA_ID ) TC