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?
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]
In the select, you only select the fields name, id, and count. You have to add B.returns to your select statement.
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]
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];
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/
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
