SQL Partial Full Outer Join

Question

This is a continuation of my previous post here. I have a table like this:

Name    Id      Amount 
Name1   1       99
Name1   1       30
Name1   9       120.2
Name2   21      348
Name2   21      21
Name3   41      99

If I run this query, thanks to Juan Carlos Oropeza:

SELECT
       [Name],
       [Id],
       count([Amount]) as 'Count'
FROM 
       table1
GROUP BY [Name], [Id]

I get this table:

Name    Id      Count 
Name1   1       2
Name1   9       1
Name2   21      2
Name3   41      1

Now I have another table like this:

Id      Return Amount
1       100
1       134.3
9       912.3
9       21
21      23.23
41      45

If I run this query:

SELECT
    [Id],
    count([Return Amount]) as 'Returns'
FROM 
    table2
GROUP BY [Id]

I get this table:

Id      Returns 
1       2
9       2
21      1
41      1

I need to combine these two tables to create a table like this:

Name    Id      Count      Returns
Name1   1       2          2
Name1   9       1          2
Name2   21      2          1
Name3   41      1          1

Here's my Full outer join statement:

SELECT
       [Name],
       [Id],
       count([Amount]) as 'Count'
FROM table1 AS A
FULL OUTER JOIN (
            SELECT
                [Id],
                count([Count]) as 'Returns'
            FROM 
                table2
            GROUP BY [Id]
) B ON A.[Id] = B.[Id]
GROUP BY [Name], [Id]

But that gives me the following table:

Name    Id      Count 
Name1   1       2
Name1   9       1
Name2   21      2
Name3   41      1

How do I get the Returns column to attach? I'm not sure which join to use in this case but my best educated answer would be a full outer join. Any ideas?

Answer 1

You can find the aggregates separately and then do the joining:

select t1.*,
    t2.*
from (
    select [Name],
        [Id],
        count([Amount]) as [Count]
    from table1
    group by [Name],
        [Id]
    ) t1
full join (
    select [Id],
        count([Return Amount]) as [Returns]
    from table2
    group by [Id]
    ) t2 on t1.[Id] = t2.[Id];

Answer 2

You just need to fetch elements from query B

SELECT
       [Name],
       [Id],
       B.Returns,
       count([Amount]) as 'Count',

FROM table1 AS A
FULL OUTER JOIN (
            SELECT
                [Id],
                count([Count]) as 'Returns'
            FROM 
                table2
            GROUP BY [Id]
) B ON A.[Id] = B.[Id]
GROUP BY [Name], [Id],B.Returns

Answer 3

You just need to add returns to your select list. Use a full outer join if you want all rows from both tables. Non matches will have null values in the fields from the non-matching table.

Left join or Right join will give all rows in one table and matches from the other. Inner join will only return rows where there is a match.

http://www.sql-join.com/sql-join-types/

Answer 4

You need to reference the joined table in your SELECT statement. And also GROUP BY that referenced column.

SELECT
       [Name],
       [Id],
       count([Amount]) as 'Count',
       B."Returns"
FROM table1 AS A
FULL OUTER JOIN (
            SELECT
                [Id],
                count([Count]) as 'Returns'
            FROM 
                table2
            GROUP BY [Id]
) B ON A.[Id] = B.[Id]
GROUP BY [Name], [Id], B."Return"

Semantics, but I consider it best practice to JOIN tables on the same aggregate level. So I'd recommend running each aggregate table separately, then joining. This prevents accidental data-duplication. Like this

SELECT
    A.Name
    ,A.Id
    ,A."Count"
    ,B."Returns"
FROM
   (SELECT
       [Name],
       [Id],
       count([Amount]) as 'Count'
    FROM 
       table1
    GROUP BY [Name], [Id]
   ) A
FULL OUTER JOIN (
            SELECT
                [Id],
                count([Count]) as 'Returns'
            FROM 
                table2
            GROUP BY [Id]
    ) B ON A.[Id] = B.[Id]

Answer 5

In the select, you only select the fields name, id, and count. You have to add B.returns to your select statement.

Answer 6

Use a full join on the aggregated results you already have. When there is a row missing on either of the tables, use COALESCE to show that result as 0 or some other value.

SELECT 
 COALESCE(t1.[Name],'Unknown') as Name
,COALESCE(t1.[Id],t2.[Id]) as ID
,COALESCE(t1.Count,0) as Count
,COALESCE(t2.[Returns],0) as Returns
FROM (SELECT
       [Name],
       [Id],
       count([Amount]) as 'Count'
      FROM table1
      GROUP BY [Name], [Id]) t1
FULL JOIN (SELECT
           [Id],
           count([Return Amount]) as 'Returns'
           FROM table2
           GROUP BY [Id]) t2 
ON t1.[Id]=t2.[Id]